Query to compare two versions of the same table

[Zaichik123]

I want to write a SQL query that compares two tables and only shows the differences between them. The tables are large and predominantly the same so as a result of running this query I'd like to be left with a new table that only shows what is in table a and not in table b, and what is in table b but not in table a.

I'm new to SQL and so far all I've been able to do is write a query that will show me what is the same in the two tables - precisely the opposite of what I want to show!

Any help or tips much appreciated.

 

[Golom]

My question is "Does 'different' mean 'different in any field?' or does it mean 'different in a key field or fields?' If its the latter then (for one key field) the generic query is:

SELECT *
FROM A LEFT JOIN B ON A.X = B.X
WHERE B.X IS NULL

UNION

SELECT *
FROM B LEFT JOIN A ON B.X = A.X
WHERE A.X IS NULL

 

[Zaichik123]

SELECT a.room_num, a.room_name, a.hce_code, a.item, a.quantity, b.room_num, b.room_name, b.hce_code, b.item, b.quantity
FROM CWT_CdeB_HCE_DataTransfer AS a INNER JOIN CWT_CdeB_HCE_DataTransfer2 AS b ON a.room_num=b.room_num

is the easy part of the query.

What I need to display are fields that are unique in either table A or B. The records will only be unique by the combination of several fields being unique. For example, where the combination of room_num and hce_code in table A is not in table B. (and also vice versa)

All of the fields will be repeated throughout the table but as I say it is only the combination of them that is then unique.

Hope this makes sense and, whatsmore hope you can help.

Thanks.

 

[Golom]

Think I understand ... but let me restate it.

You have records in the two tables and each record in A exists in B in a KEY sense. For example, if the key is 'room_num' then at least one record for a particular value of 'room_num' will exist in each table. THEN, you want to know, for each 'room_num', are there records that are different in other fields? If that's the case then lets try this:

SELECT "Table CWT_CdeB_HCE_DataTransfer" As

, a.room_num, a.room_name, a.hce_code, a.item, a.quantity
FROM CWT_CdeB_HCE_DataTransfer AS a INNER JOIN CWT_CdeB_HCE_DataTransfer2 AS b
ON a.room_num=b.room_num
WHERE a.room_name & a.hce_code & a.item & Str$(a.quantity) <>
b.room_name & b.hce_code & b.item & Str$(b.quantity)

UNION

SELECT &quot;Table CWT_CdeB_HCE_DataTransfer2&quot; As

, a.room_num, a.room_name, a.hce_code, a.item, a.quantity
FROM CWT_CdeB_HCE_DataTransfer2 AS a INNER JOIN CWT_CdeB_HCE_DataTransfer AS b
ON a.room_num=b.room_num
WHERE a.room_name & a.hce_code & a.item & Str$(a.quantity) <>
b.room_name & b.hce_code & b.item & Str$(b.quantity)

This simply concatenates all the other fields in A and in B and compares the resultant strings. If they're not equal then the record is reported in the result. I used the union so that you see both the record as it is in table 'A' and as it is in Table 'B'.

If you have multiple key fields then add them to the ON clause and remove them from the concatenation. For example

ON a.room_num=b.room_num AND a.hce_code = b.hce_code
WHERE a.room_name & a.item & Str$(a.quantity) <>
b.room_name & b.item & Str$(b.quantity)

 

[Zaichik123]

Thanks for your help so far Golom, but rather than NOT show any duplicated records in the two tables and only display records that are unique to one or the other of the tables - it is just showing that which matches.

If you imagine you have two tables which have 10 identical records, effectively this query should report nothing. If you had a table of ten records and another table of 11 records with the only difference being the 1 additional record - that would be all that was displayed. (If this was essentially 10 columns by 10 rows = 10 records, you wouldn't have to include all 10 column headings in the query would you?)

I would have thought if you wrote
WHERE a.room_name & a.hce_code & a.item & Str$(a.quantity) = b.room_name & b.hce_code & b.item & Str$(b.quantity)
then this would display just matched records, and by typing
WHERE a.room_name & a.hce_code & a.item & Str$(a.quantity) <> b.room_name & b.hce_code & b.item & Str$(b.quantity)
it would just show the diferences, but for whatever reason it doesn't.

I also thought maybe if you wrote
WHERE a.room_name & a.hce_code & a.item & Str$(a.quantity) < b.room_name & b.hce_code & b.item & Str$(b.quantity)
and then after the union,
WHERE a.room_name & a.hce_code & a.item & Str$(a.quantity)
> b.room_name & b.hce_code & b.item & Str$(b.quantity)
but still no joy.

If you can help anymore it would be much appreciated.