Jump to content

Archived

This topic is now archived and is closed to further replies.

Bhaal

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

Recommended Posts

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...

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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...)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.