Query to get all combinations from two tables based on ID

[mmiram]

Hi all:

I have two tables: march05 and march06

Both tables have the following fields:

ID
Name
Qty
Amount

The problem is march05 has some items that march06 does not and vice versa. There are other items that are present in both tables. I want to write a query that will generate the following result

ID
Name
Qty05
Amount05
Qty06
Amount06

If an item present in both years the respective values from "05" and "06" are shown fpr that item. However, if the items are not common, then the values for that year are shown for that item and the the cells are left blank for the other year.

Please let me know if this is clear enough. If not, I will try to post with examples.

Thanks.

 

[lameid]

The easy way to do this is with a Full Outer Join which is supported by SQL server but not supported by Access.

In Access you are stuck righting a Left or Right outer join query and then using union to get the values from the other table...

Select March05.*, March06.*
From March05 Left Join March06 ON March05.ID = March06.ID

Union All

Select March06.*
From March06
Where March06.ID Not IN (select ID from March05)

 

[PHV]

One way:
SELECT A.ID, A.Name, A.Qty AS Qty05, A.Amount AS Amount05, B.Qty AS Qty06, B.Amount AS Amount06
FROM march05 AS A LEFT JOIN march06 AS B ON A.ID = B.ID
UNION SELECT B.ID, B.Name, A.Qty, A.Amount, B.Qty, B.Amount
FROM march05 AS A RIGHT JOIN march06 AS B ON A.ID = B.ID
WHERE A.ID Is Null

Another way:
SELECT ID, Name
, Sum(IIf(orig=5,Qty,0)) AS Qty05, Sum(IIf(orig=5,Amount,0)) AS Amount05
, Sum(IIf(orig=6,Qty,0)) AS Qty06, Sum(IIf(orig=6,Amount,0)) AS Amount06
FROM (
SELECT ID, Name, 5 AS orig, Qty, Amount FROM march05
UNION SELECT ID, Name, 6, Qty, Amount FROM march06
) AS U
GROUP BY ID, Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886