LEFT JOIN confusion

[ntroycondo]

I have 3 tables for image DB.

I want to return photo name and photographer name for photos taken in Chicago.

3 tables are:

HPhotograher with columns photographerid, name

HLocation with columns locationid, name

HPhoto with columns photoid, filename, photographerid, locationid

 

My below query returns empty set with errors. So i know I'm confusing the query somehow.

 

 

SELECT HPhoto.filename, HPhotographer.name

FROM HPhotographer

LEFT JOIN HPhoto ON HPhoto.photographerid = HPhotographer.photographerid

LEFT JOIN HLocation ON HLocation.locationid = HPhoto.locationid

WHERE  HLocation.locationid = HPhoto.locationid AND HPhoto.locationid = 'chicago'

ORDER BY HPhoto.date ASC;

 

[DavidAM]

I don't see why you need a LEFT JOIN. A regular JOIN should do it.

1) Take out the first condition in the WHERE clause, you are just repeating the relationship already stated in the second JOIN ... ON;

2) is locationid a string or a number? Don't you want the name from the location table in the WHERE clause?

 

 

SELECT HPhoto.filename, HPhotographer.name
FROM HPhotographer
JOIN HPhoto ON HPhoto.photographerid = HPhotographer.photographerid
JOIN HLocation ON HLocation.locationid = HPhoto.locationid
WHERE  HLocation.name = 'chicago'
ORDER BY HPhoto.date ASC;