List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 11 1999 2:13pm
Subject:Re: SQL join question
View as plain text  
Willem Bison wrote:
> 
> I need to list items in a table that do not have a corresponding item in a
> related table.
> The table 'forum' contains the complete list, the table 'msg' contains
> 'orphaned' items. The tables are linked with field 'id'.
> I looked in the archive and tried this based on a posting I found:
> 
> select forum.id from forum left join msg using (id) where msg.id=NULL limit
> 5;
> 
> This query gives an empty result set, while a record with id=1 exists in
> 'forum' but not in 'msg' (so '1' is an item in the desired result set).
> 
> What is the correct SQL statement ?

Hi Willem

You never ever can test for NULL fields with = instead use IS NULL:
SELECT forum.id FROM forum LEFT JOIN msg USING (id) WHERE msg.id IS NULL LIMIT 5;

Thread
SQL join questionWillem Bison11 Mar
  • Re: SQL join questionChristian Mack11 Mar
  • Re: MySQL 3.23 alpha released (problems/fixes)Paul DuBois5 Jul
    • MySQL 3.23 & autoincrementBarry5 Jul
      • Re: MySQL 3.23 & autoincrementPaul DuBois5 Jul
        • Re: MySQL 3.23 & autoincrementMartin Ramsch5 Jul
          • Re: MySQL 3.23 & autoincrementPaul DuBois5 Jul
            • Re: MySQL 3.23 & autoincrementMichael Widenius7 Jul
        • Re: MySQL 3.23 & autoincrementDick Griffin5 Jul
        • Re: MySQL 3.23 & autoincrementMichael Widenius7 Jul
      • MySQL 3.23 & autoincrementMichael Widenius7 Jul