select records from 1 table where field !found in another

[kenndot]

I have two tables. They both have a field - memberid. There are two types of members. For explanation purposes, I"ll use Premium Members and NON Prem. Members. Table1 contains ALL members and table 2 contains only premium members.

I need a query that gets only records (from the table with ALL members) that are found in table2 and also a query that gets only records (from the table with all members) that are NOT found in table2. I'm having some trouble getting it right.

This is what I have right now and I'm pretty sure it's not working correctly.

select memberid, tdate from nonprmshare right join prmshare on nonprmshare.memberid = prmshare.memberid
where nonprmshare.memberid != prmshare.memberid

Any help is appreciated...

Thanks

 

[wgcs]

Here's code that should work (I tested it locally and it works on related tables I have):

SELECT NonPrmShare.memberid, tdate ;
FROM nonprmshare ;
WHERE EXISTS ( SELECT * FROM prmshare ;
WHERE nonprmshare.memberid = ;
prmshare.memberid )

and

SELECT NonPrmShare.memberid, tdate ;
FROM nonprmshare ;
WHERE NOT EXISTS ( SELECT * FROM prmshare ;
WHERE nonprmshare.memberid = ;
prmshare.memberid )

 

[Eguy]

Hi kenndot

I do this type of matching quite often and found that this works quite efficiently:

SELECT memberid, tdate ;
FROM nonprmshare ;
WHERE IN (SELECT DISTINCT memberid FROM prmshare)

- and -

SELECT memberid, tdate ;
FROM nonprmshare ;
WHERE !IN (SELECT DISTINCT memberid FROM prmshare)

Ed

Please let me know if the sugestion(s) I provide are helpful to you.
Sometimes you're the windshield... Sometimes you're the bug.

 

[BlindPete]

wgcs and Eguy

I see the question popping up from time to time. You all should consider submiting a FAQ. -Pete

 

[kenndot]

Great idea! Thank you so much for the info, it works great! All of them!

 

[kenndot]

Does this not work in the command window? I'm typing this exactly as I see it...

I get Function Name is Missing error...

Help!

 

[rgbean]

If you are entering this in the command window EXACTLY as shown, make sure you use Ctrl+Enter after each ; , and then Enter on the last line.

Rick

 

[HotHand]

To select members in table1 that are also in table2

select memberid, tdate from table1 where memberid in (select memberid from table2)

To select members in table1 that are NOT in table2

select memberid, tdate from table1 where memberid not in (select memberid from table2)

NOTE: "select * from table1" works the same.