list all ID matches

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables. I would like to compare the two tables (IDs) and list all
matching IDs to one table.

Result
Table94 Table95 TableAll
ID ID ID
123 123 123
456 789 123
789 123 123
789
789
 
Hi,



Make a query:


SELECT id FROM Table94 UNION ALL SELECT id FROM Table95



save it, say, under the name of qu1.

SELECT a.id
FROM qu1 As a INNER JOIN
(SELECT id
FROM qu1
GROUP BY id
HAVING COUNT(*) >1) As b
ON a.id=b.id


should give the repeated id having a count >1





Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,
I need to add additional information.
I also need the records in table95 that do not repeat in table94
The end result would have All of the records in table95 plus any matching
ID records from table94 and again if for example three records match I
need all three listed.
Thanks in advance
Harold
 
Hi,


SELECT a.id
FROM table94 As a LEFT JOIN (SELECT DISTINCT id FROM table95) As b
ON a.id = b.id
WHERE b.id IS NULL

UNION ALL

SELECT id
FROM table95



Hoping it may help
Vanderghast, Access MVP
 
Back
Top