Access to SQL: select a connection, display both fields

Question

I have this SQL:

SELECT Ph.Account, Ph.Ct FROM Ph UNION SELECT Rx.Account, Rx.Ct FROM Rx; 

Which works fine, but the Ph.Ct and Rx.Ct fields may not always be the same. Therefore, I wanted to display both of them, but in the request only the "Ct" field is displayed, and not both.

How can I show this?

Here ph :

 12685 3 29568 1 38771 2 

Here rx :

 10657 1 12685 2 68781 2 79874 1 

What I want to extract from the request:

 Account ph.ct rx.ct 10657 1 12685 3 2 29568 1 38771 2 68781 2 79874 1 

UNION gets the correct data set (about 800 results), but not the correct fields. Any JOINs I tried do not give the correct data set (about 300 results in total).

Answer 1

You need a full external connection. For each Account value appearing in any of these tables, this will give the corresponding Ct values ​​for each table if the specified Account value is displayed and null otherwise.

 select Account,Ph.Ct as ph_ct,Rx.Ct as rx_ct from Ph full outer join Rx on (Ph.Account=Rx.Account); 

Edit: Since Access does not seem to support full outer join (for some terrible reason), you can achieve the same effect with joining the left join with the join right:

 select Ph.Account, Ph.Ct as ph_ct, Rx.Ct as rx_ct from Ph left join Rx on (Ph.Account=Rx.Account) union select Rx.Account, Ph.Ct as ph_ct,Rx. Ct as rx_ct from Ph right join Rx on (Ph.Account=Rx.Account); 

which is also equivalent (possibly faster):

 select Ph.Account, Ph.Ct as ph_ct, Rx.Ct as rx_ct from Ph left join Rx on (Ph.Account=Rx.Account) where (Rx.Account IS NULL) union all select Rx.Account, Ph.Ct as ph_ct, Rx.Ct as rx_ct from Ph right join Rx on (Ph.Account=Rx.Account); 

Answer 2

You cannot have a full external connection in MS Access, therefore:

 SELECT m.Account, Ph.Ct, Rx.Ct FROM ((SELECT Ph.Account FROM Ph UNION SELECT Rx.Account FROM Rx) As m LEFT JOIN Ph ON m.Account = Ph.Account) LEFT JOIN Rx ON m.Account = Rx.Account 

Answer 3

I do not think you want union . Do you want to join . If you join the fields in Account, you can also show Rx.Ct and Ph.Ct.

In union it lists all rows from one table stacked on top of another table, excluding duplicates. Each row is from a different table.

In join each row is a combination of two tables, which means that columns that are not joined can contain different values.

Here you go:

 SELECT Ph.Account, Ph.Ct, Rx.Ct FROM Ph INNER JOIN Rx ON Ph.Account=Rx.Account; 

Answer 4

You want to join , not union :

 select coalesce(Ph.Account, Rx.Account) as Account Ph.Ct, Rx.Ct from Ph full outer join Rx on Ph.Account = Rx.Account 

You can change inner to the type of connection you want. Read more about the different types of compounds here .

union used when you want to take one set of results and add them to the set of rows of another result. join used when you want to take one set of results and add them to the set of columns of another result.

Answer 5

For future reference on the UNION Operator only:

The UNION statement is used to combine a result set of two or more SELECT statements.

Note that each SELECT statement in UNION must have the same number of columns. Columns must also have similar data types. In addition, the columns in each SELECT statement must be in the same order.

Note. The default UNION operator selects only different values. To allow duplicate values, use UNION ALL.

For more information: UNION statement