CODEWITHSUNDEEP
phpmysql
Dylan Cross
Dylan Cross

Reputation: 5986

MYSQL query doesn't work if row doesn't exist in a table

I have a query that selects from two tables, however if there aren't any values in one of the tables it doesn't return anything

SELECT albums.*, MAX(albums.date) AS newDate 
FROM albums, albumData
WHERE albums.userID='$id' 
AND albums.state='0' 
AND (
 albums.albumID=0
 OR
 albumData.id=albums.albumID
 AND albumData.state='0'
) 
GROUP BY albums.albumID, albums.userID
ORDER BY newDate DESC

If I do part of it in mysql and the other part in php it works as expected:

SELECT albums.*, MAX(albums.date) AS newDate 
FROM albums WHERE userID=$id AND albums.state='0' 
GROUP BY albums.albumID ORDER BY newDate DESC

then:

if($quer['id']&&$quer['state']==0||$albumID==0)
{//do my stuff here }

Sp i need to combine the two ultimately.

Upvotes: 1

Views: 224

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You are using old style joins wich are implicit INNER JOINs. An INNER JOIN by definition only returns records when there is a match between both tables.

Convert the statement to use explicit joins and use the LEFT JOIN syntax.

SELECT  albums.*
        , MAX(albums.date) AS newDate  
FROM    albums
        LEFT JOIN albumData ON albumData.id = albums.albumID
WHERE   albums.userID='$id'  
        AND albums.state='0'  
        AND (albums.albumID=0  OR albumData.state='0')  
GROUP BY 
        albums.albumID
        , albums.userID 
ORDER BY 
        newDate DESC

For the record: you should stop using old style join syntax. It will eventually loose support from your DBMS, is less readable and less maintainable.

Upvotes: 4

Related Questions