Friday, 23 August 2013

MySql LEFT JOIN within LEFT JOIN

MySql LEFT JOIN within LEFT JOIN

I am trying to do the following MySql query for a search box. I am trying
to return the "Album" info (Title etc) while including a thumbnail of the
first image in that album. However, I have to look up two tables to get
the image info. First, the photos_albums that contain all the images in
that album, get the first image ID from that table, then look up that
image info in the photos table. I believe the problem I am having, is that
I need to tell the first LEFT JOIN to limit the query to 1, but I have had
no luck doing this. I think I need to do a JOIN within a JOIN? Any help on
this would be much appreciated.
SELECT albums.title, albums.title_url, photos.path, photos.medType,
photos.vpath
FROM albums
LEFT JOIN photos_albums
ON photos_albums.album_id = albums.id
LEFT JOIN photos
ON photos_albums.photo_id = photos.id
WHERE albums.user = '$site_user'
AND (
albums.title LIKE '$keyword%'
OR albums.title LIKE '% $keyword%')
LIMIT 6

No comments:

Post a Comment