finding distinct records in tbl_A that are in tbl_B

[krazieddi]

tbl_A has column "ID" with duplicates. tbl_B has column "ID" with distinct values.

i want to find all the distinct IDs in tbl_A that are present in tbl_B.

I've tried the following with no luck:

SELECT DISTINCT tbl_A.ID
FROM tbl_A LEFT JOIN tbl_B ON tbl_A.ID = tbl_B.ID
WHERE tbl_A.ID IN tbl_B.ID;

I know i'm doing something wrong but i can't figure it out.

PLEASE HELP!


Thanks!


-ed

 

[plog]

First, we can only help you if you tell us how you know you are doing something wrong. Error message? Unexpected results? Computer on fire?

Second, this SQL should do what you typed you wanted:

SELECT tbl_A.ID
FROM tbl_A
INNER JOIN tbl_B on tbl_A.ID=tbl_B.ID
GROUP BY tbl_A.ID

A LEFT JOIN includes all records from the first table (tbl_A) and any matches from the second (tbl_B). That's not what your explanation said you wanted, so an INNER JOIN is the one for you.

 

[krazieddi]

I tried the INNER JOIN and got a "Type mismatch in expression." error.


and the computer was on fire.


any help with the error message?

 

[plog]

My guess is the ID fields in each table are of a different type. Is tbl_A.ID numeric and tbl_B.ID text? Or vice versa?

If so, try this SQL

SELECT tbl_A.ID
FROM tbl_A INNER JOIN tbl_B ON (tbl_A.ID)*1 = (tbl_B.ID)*1
GROUP BY tbl_A.ID;

It converts them both to numbers then makes the match between the tables.