CODEWITHSUNDEEP
mysql
user516883
user516883

Reputation: 9378

mysql query not recieving any data on my left join

I have a query that on the left join part should either return data or null. But even if there is existing records its not returning any data. The twist to the left join portion is that I would like to only retrieve one record if it exist. Thanks for any help.

select a.*,p.thumbnailphotopath as AlbumPicture 
from (select * from album_access) ac
inner join (select * from albums) a on a.ID = ac.AlbumID
left join (
    select * from photos 
    where IsProcessed = 1 order by DateUploaded desc limit 1
) p 
on a.ID = p.AlbumID #should return one if exist.
where ac.AccessUserID = '35e44a8e-643a-4c4f-8a46-59911a1e7c53' 
  and ac.FullControl = 1 and a.Private = 1

Upvotes: 0

Views: 25

Answers (1)

liquorvicar
liquorvicar

Reputation: 6106

First you can just join on a table name and don't need to join on a whole SELECT * FROM statement.

Second, you should try not to use SELECT * and instead SELECT the columns you want.

But I think the problem with your LEFT JOIN is that you're joining on a sub-query that will only return one result, which will be the entry in photos that was last uploaded irrespective of which albumID it belongs to. If you want the entry in photos with the last uploaded date for each row, try something like this

SELECT a.*,p.thumbnailphotopath AS AlbumPicture 
FROM album_access ac
INNER JOIN albums a ON a.ID = ac.AlbumID
LEFT JOIN (
    SELECT albumID,MAX(DateUploaded) FROM photos 
    WHERE IsProcessed = 1 GROUP BY albumID
) p ON a.ID = p.AlbumID #should return one if exist.
WHERE ac.AccessUserID = '35e44a8e-643a-4c4f-8a46-59911a1e7c53' 
  AND ac.FullControl = 1 
  AND a.Private = 1

Upvotes: 2

Related Questions