[thelist] SQL Issues with NULL Values in Column

Ken Schaefer ken at adOpenStatic.com
Thu Jun 3 09:06:56 CDT 2004


Instead of putting the join in the WHERE clause (which is deprecated), you
should use an explicit JOIN. You want a LEFT OUTER JOIN.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "RUST Randal" <RRust at COVANSYS.com>
Subject: [thelist] SQL Issues with NULL Values in Column


I have three tables in my MySQL database:

boots_artists
boots_labels
boots_bootlegs

Using PHP, I want to view a detail of a record in boots_bootlegs, which
has columns for 'artistID' and 'labelID.' I want to pull values from
'boots_artists.artistName' and 'boots_labels.labelName' based on the
appropriate IDs in the boots_bootlegs table. The problem seems to be
that 'labelName' can be NULL, which returns no records when I run this
query.

SELECT
boots_artists.artistName, boots_labels.labelName,
boots_bootlegs.artistID, boots_bootlegs.labelID
FROM
boots_artists, boots_labels, boots_bootlegs
WHERE
boots_bootlegs.bootlegID =  '1'
AND
boots_artists.artistID = boots_bootlegs.artistID
AND
boots_labels.labelID=boots_bootlegs.labelID

I'm confused about what to do here. I want to get results, even if the
'labelID' has a NULL value, but obviously I'm not going to because there
is not match.

----------
Randal Rust




More information about the thelist mailing list