list all ID matches

H

Harold

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
 
M

Michel Walsh

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
 
H

Harold

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
 
M

Michel Walsh

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
 
Top