List of records in table A without corresponding record in table B

[Bhaal]

Hey all,

I'd like to create a list of records in one table that do not have a corresponding (join?) record in another table.

I have one table called 'members' that has a MemberID field.

A second table called 'listings' holds records that are joined by MemberID.

Is there an "easy" way to get a list of records in the 'members' table that DO NOT have a corresponding record in the 'listings' table?

Thanks for any help...

[Bhaal]

OK, I found the answer I think:

[code]

$q1 = "SELECT * FROM members WHERE MemberID NOT IN (SELECT DISTINCT MemberID FROM listings)";

[/code]

Sorry to bother y'all. But perhaps someone else can benefit from this...

[Barand]

Subqueries can be slow. You will probably find that this executes much faster

[code]$ql = "SELECT m.* FROM members m
     LEFT JOIN listings l ON m.MemberID = l.MemberID
     WHERE l.MemberID IS NULL";[/code]

[Bhaal]

Thanks for the advice Barand...but your version does not give any results. (The other version produces verifiably true results.)

However, another problem:

"My" version of the query works fine on my local host, but when I upload it to a hosting server it produces an error:

[code]
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select distinct MemberID from pppx_listings)' at line 1
[/code]

Very strange. Any insights?

[Barand]

MySQL versions 4.1+ support subqueries.

LEFT JOIN available in all versions.

Check table and column names in my code - it's a traditional tried and tested means of finding unmatched records

[Bhaal]

Just found out why I'm getting an error.

The host server's version of MySQL is 4.0.2.0 which doesn't support sub-queries. LAME.

Checked and rechecked. (Are you sure that NULL is correct? I guess it'd have to be...)