Simple Difference query

[conipto]

Hey guys,
Suppose I have two tables with the same schema, and using one field of each, can I easily get the difference from both tables with a single query?

Something like

'Table A
ID  qty
e4 1
e5 20
e6 2
e7 5

'Table B
ID  qty
e4 1
e5 22
e3 12

'Producing
ID  qtyfromA   qtyfromb
e3    0                12
e5    20               22
e6    2                 0
e7    0                 5

Where it joins the tables, but only tells me what's different, and what's entirely missing from both sides of the join? I'm not too hot with SQL. I can make it pull the differences where both tables have the same ID number with different qty numbers, but not where either table is missing both..

Bill

[salvelinus]

Details would be dependant on what db/vb versions you're using. I think you can do what you want in sql, but would have to use Iif or Nz (Access) or similar SQL Server functions.
For instance, a normal Join query would return either nothing or Null for TableA.e3, depending. You need some way to convert Nothing or Null to 0.
I could test it out in Access, but there's lots better SQL gurus here than me.

[brucevde]

In SQL Server you could use a "Derived" table created from a Union query. I don't think Access supports Derived tables.

Select Id, Sum(QtyA) As QtyFromA, Sum(QtyB) as QtyFromB
From
   (Select Id, Qty As QtyA, 0 As QtyB From TableA
    Union All
    Select Id, 0, Qty From TableB
   ) As DataSet
Group By Id
Having Sum(QtyA) <> Sum(QtyB)

[Edit]
I keep forgetting about Full Outer Joins.

Select IsNull(TableA.Id,TableB.Id) As ID, IsNull(TableA.Qty,0) As QtyFromA, IsNull(TableB.Qty,0) AS QtyFromB
From TableA Full Join TableB On TableA.Id = TableB.Id
Where IsNull(TableA.Qty,0) <> IsNull(TableB.Qty,0)

[conipto]

I am using Access, unfortunately, which means it won't allow me to use a Full Join. And though it doesn't support derived tables, I can just query a query and get the same result, I suppose. I'll give it a shot, thanks.

Bill

[conipto]

The union all and the sum worked out well. Seems a bit wasteful to have to use two queries and so much "fake" zero data, but this app will never feel it, so thanks, that'll do

Bill

[salvelinus]

In Access you also have the Nz() function, which will convert null values to either an empty string (default) or a value, such as 0, that you supply.

[haimk63]

If you have a main ID table such as:

Main Table
ID Text
e1 aaa
e2 bbb
e3 ccc
e4 ddd
e6 eee
e7 fff

You can query the main table for the Id's and left join the other two tables A and B for quantities.