MySQL if true add SELECT and WHERE

Question

I have a query like this:

SELECT tm.first_name, tm.last_name, tm.id, i.thumb 
FROM team_members tm, images i, tm_image tmi 
WHERE tm.id = tmi.tm_id AND tmi.image_id = i.id

Works great, I get back the name etc. and the image which is linked to the individual by a table like this:

individual_id -> individual_id - image_id <- image_id

The problem is that if the individual has no image attached to them then the query returns nothing. What I would like is for the query just to return NULL if there is no image data. Would I use an if statement?

Thanks!

Answer 1

You need to do a LEFT JOIN to solve your problem

SELECT tm.first_name, tm.last_name, tm.id, i.thumb 
FROM team_members tm
LEFT JOIN tm_image tmi ON (tmi.image_id = i.id)
LEFT JOIN images i ON (tm.id = tmi.tm_id);

Untested, may contain errors.

With the query above if the member does not have an image the value for tm.id and i.thumb will both be NULL

You can find some examples here : http://phpweby.com/tutorials/mysql/32

Answer 2

Use LEFT JOIN. Like this:

SELECT 
   tm.first_name, 
   tm.last_name, 
   tm.id, 
   i.thumb 
FROM 
 team_members tm
 LEFT JOIN tm_image tmi 
   ON tm.id = tmi.tm_id
 LEFT JOIN images i
   ON tmi.image_id = i.id