Find the diffs between to Queries

[JONBOY74]

Hi all

I have 2 queries, they both have the same amount of fields (Field A has the products name, and field 2 has the sum total of products bought). The data for each of these tables comes from different sources.

Here's my problem I want to be able to take the results of 2 queries and find any differences in the data.

eg

QRY 1 QRY2 Diff
Product Amount Product Amount
Cars 50 Cars 20 30

I've tried the following:

SELECT QRY1.Amount-QRY2.Amount AS Diffs, QRY1.PRODUCT
FROM QRY1, QRY2
WHERE QRY1.Product=QRY2.Product;

This works, but if one of the products doesn't show on a table, then it doesn't select the product at all. What I need is for the diff to show.

Does this make any sense & can anyone help me

Thanks

JON

 

[cmmrfrds]

If it is possible for a value(join key) in table1 to not be in table2 and for a value in table2 to not be in table1, then you will probably need to do both a left and right outer join.

This will introduce another problem. If table1 has a value and table2 does not then the values in table2 will be null. You cannot perform the difference operation on a null column. This is resolved by the isnull(yourname,a default).
i.e. isnull(Amount,0).

'---An example of outer joins on the pubs database.
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors
AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
LEFT OUTER JOIN publishers AS z
ON a.city = z.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Hopefully this example can be a guide for your requirements. This will get both sides where values can exist in one table but not the other.