CODEWITHSUNDEEP
sqlleft-joinisnull
rex
rex

Reputation: 11

Left join, ISNULL, IFNULL don't work

CREATE TABLE IF NOT EXISTS `tblflash` (
  `FID` int(11) NOT NULL AUTO_INCREMENT,
  `fname` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`FID`)
) 

CREATE TABLE IF NOT EXISTS `tblcount` (
  `ID` int(50) NOT NULL AUTO_INCREMENT,
  `SID` int(50) NOT NULL,
  `FID` int(11) NOT NULL,
  `sDateTime` datetime NOT NULL,
  `elaspedTime` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
)


$sqldate1 =
    "SELECT distinct tblflash.FID, tblflash.fname, IFNULL(sum(tblcount.elaspedTime),0)
    FROM tblflash 
    left outer JOIN tblcount 
    ON tblflash.FID = tblcount.FID 
    WHERE tblcount.SID='".$_SESSION['SID']."'
    ORDER BY tblflash.FID";

let's say there are 10 rows in tblflash has 10 rows and 5 rows in tblcount. the record shown shows in 5 rows only. I would like to show all the fname with no duplication, and if null set to 0.

What's wrong?

Upvotes: 0

Views: 433

Answers (3)

Andriy M
Andriy M

Reputation: 77707

Your WHERE condition is applied to the result of the join, not specifically to tblcount rows. And the join returns NULLs in all tblcount columns when there's no match between the two tables. As a result, NULL = 'whatever' doesn't evaluate to true and the row isn't returned.

Either add OR tblcount.SID IS NULL to WHERE as @cjk has suggested or make the WHERE condition part of the ON clause, like this:

$sqldate1 =
    "SELECT distinct tblflash.FID, tblflash.fname, IFNULL(sum(tblcount.elaspedTime),0)
    FROM tblflash 
    left outer JOIN tblcount 
    ON tblflash.FID = tblcount.FID 
    AND tblcount.SID='".$_SESSION['SID']."'
    ORDER BY tblflash.FID";

Upvotes: 1

shibormot
shibormot

Reputation: 1638

There are no data for SID in tblcount for other 5 rows

Upvotes: 0

cjk
cjk

Reputation: 46465

Your WHERE tblcount.SID='".$_SESSION['SID']."' is making the query act as an INNER JOIN - you need to add or tblcount.SID IS NULL

Upvotes: 1

Related Questions