eladr
asked on
SQL query to retrieve changes
Hi…
Let's say I have 2 tables with id \ loginname
Table A
1 eladr
2 billc
3 tonyu
Table B
1 eladr
2 billca
3 tonyu
4 davidv
I need to write sql query to retrieve only records with changes comparing the tables.
In this case only records 2 and 4 are different (2 is different because of the loginame and 4 is new)
Let's say I have 2 tables with id \ loginname
Table A
1 eladr
2 billc
3 tonyu
Table B
1 eladr
2 billca
3 tonyu
4 davidv
I need to write sql query to retrieve only records with changes comparing the tables.
In this case only records 2 and 4 are different (2 is different because of the loginame and 4 is new)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to have a subquery that creates a unique list of all the ID's first, then LEFT JOIN that to the 2 tables and pick up the nulls or differences. E.G. with TableA and Table B, fields ID and Name:
Select TableIDList.ID, TableA.Name As NameA, TableB.Name As NameB
FROM ((SELECT ID FROM TableA
UNION
SELECT ID FROM TableB) As TableIDList LEFT JOIN TableA ON TableIDList.ID = TableA.ID) LEFT JOIN TableB ON TableIDList.ID = TableB.ID
WHERE TableA.Name <> TableB.Name OR TableA.Name IS NULL OR TableB.Name IS NULL
Select TableIDList.ID, TableA.Name As NameA, TableB.Name As NameB
FROM ((SELECT ID FROM TableA
UNION
SELECT ID FROM TableB) As TableIDList LEFT JOIN TableA ON TableIDList.ID = TableA.ID) LEFT JOIN TableB ON TableIDList.ID = TableB.ID
WHERE TableA.Name <> TableB.Name OR TableA.Name IS NULL OR TableB.Name IS NULL
Sorry - I thought this post came from the MS Access forum,which does not support the FULL OUTER JOIN in it's SQL. If the database system you are using supports the FULL OUTER JOIN that would be best...
ASKER
10x
select *
from tableB b
left join tableA a
on a.id = b.id
and a.name <> b.name