Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 12 April 2006 - 12:55 AM

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

#2 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 12 April 2006 - 02:00 AM

OK, I found the answer I think:


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


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

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 12 April 2006 - 11:06 PM

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

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 13 April 2006 - 06:52 AM

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:

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

Very strange. Any insights?

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 13 April 2006 - 07:01 AM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 Bhaal

Bhaal
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 13 April 2006 - 07:15 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users