Thursday, 30 August 2018

MYSQL where statement does not work as expected


I'm trying to count the number of rows in this query, however it's not working as expected, this returns an extra row, it should be 12, but it's 13.


$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id
FROM albums, albumData
WHERE
(albumData.id=albums.albumID OR albums.albumID=0)
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));

When I remove the OR statement part, and not count the rows with albumID=0, it returns 11. There is only one row where the albumID is 0, but it counts it as two?
$numPhotos = mysql_num_rows(mysql_query("
SELECT albums.id
FROM albums, albumData
WHERE
albumData.id=albums.albumID
AND
albums.userID=$id
AND albums.state=0
AND albumData.state=0
"));


Try to write query using JOIN:
SELECT albums.id FROM albums
LEFT JOIN albumData
   ON albums.albumID=albumData.id
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

or
SELECT albums.id FROM albums
LEFT JOIN albumData
   ON albums.albumID=albumData.id
WHERE albums.userID=$id
  AND albums.state=0
  AND albumData.state=0

Does this solve your trouble?
EDITED:
Try this
SELECT DISTINCT albums.id FROM albums
INNER JOIN albumData
   ON (albums.albumID = albumData.id OR albums.albumID = 0)
  AND albums.state=0
  AND albumData.state=0
WHERE albums.userID=$id

0 comments:

Post a Comment