CODEWITHSUNDEEP
mysqlsql
user2788110
user2788110

Reputation: 45

MySQL JOIN to return correct value from secondary table

I'm joining two tables together, a "Photos" table and "Locations" table. Each location may or may not have a default picture associated with it, and if it does I want to return that photo name. If it doesn't then return null. The default image for each location (if it exists) is always the image that has the "PictureSort" value of 1.

From my first SQL attempt below, if I use a WHERE statement, then I don't receive the null locations (where photos don't exist for that location).

SELECT a.LocationID, a.LocationName, b.PictureSort, b.PictureName
FROM Locations AS a 
LEFT JOIN Photos AS b ON a.LocationID = b.LocationID
WHERE b.PictureSort = '1'
GROUP BY a.LocationID

I also tried using the MIN() function in mySQL to say show me the lowest value in the PictureSort column, and while that works for that column, the column right next to it PictureName doesn't follow suit and return the associated value to the PictureSort column.

SELECT a.LocationID, a.LocationName, MIN(b.PictureSort) AS PictureSort, b.PictureName 
FROM Locations AS a 
LEFT JOIN Photos AS b ON a.LocationID = b.LocationID
GROUP BY a.LocationID

Any help is greatly appreciated. I want this to work, but I'm also looking for the least expensive query possible to get this done.

Thanks!

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Conditions on the second table should be in the on clause for a left join.

Also, your table aliases should be related to the names of the table. And the GROUP BY should not be needed. So try this:

SELECT l.LocationID, l.LocationName, p.PictureSort, p.PictureName
FROM Locations l LEFT JOIN
     Photos p
     ON p.LocationID = l.LocationID AND b.PictureSort = '1';

If PictureSort is stored as a number, then drop the single quotes. Don't mix data types.

Upvotes: 1

Related Questions