From: Christian Mack
Date: March 11 1999 2:13pm
Subject: Re: SQL join question
List-Archive: http://lists.mysql.com/mysql/21
Message-Id: <36E7CF8E.4748DF23@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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;