List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 4 1999 5:57pm
Subject:Re: How to find entries NOT in both tables
View as plain text  
On Sat, 1999-09-04 16:37:20 +0800, Mark Hendley wrote:
> The releveant parts of the two tables are:
> 
> Table User: USID (User ID that is Primary Key)
> Table UDetails: UDID (Primary Key) and USID (connection to User)
> 
> Now for each entry in UDetails there 'should' be a corresponding
> entry in User. UDetails table stores a users Details and User stores
> his/her username and the MD5 hash of their username+password (USID).
> 
> However, doing a count(*) for each table I find UDetails has 940 and
> User has 948 ! :)
> 
> Is there a way to join the tables and show what is NOT common to
> both with regard to the USID field.
[...]
> I'm sure the answer is fairly easy :) but too many hours coding and
> flu are interfering with normal rational thought on this end.

The trick is to use a LEFT JOIN and test for the rows, where no
matching row of the second table has been found:

  SELECT UDetails.USID
  FROM   UDetails LEFT JOIN User  ON UDetails.USID=User.USID
  WHERE  User.USID IS NULL;

Try this query to see what happens internally:
  SELECT *
  FROM   UDetails LEFT JOIN User  ON UDetails.USID=User.USID;

Happy weekend wishes,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
How to find entries NOT in both tablesMark Hendley4 Sep
  • Re: How to find entries NOT in both tablesMartin Ramsch4 Sep