MySQL JOIN, via PHP, when there is no data in one of the tables?

Question

How do you deal with a join statement if one of the tables you are joining does not have any data for the user in it.

For example:

-user_1 is listed in all tables.

-user_2 is listed in table_1, table_2, and table_3 but not in table_4.

The following query fails because user_2 is not in table_4.

$query = "SELECT table_1.username, table_2.car, table_3.bike, table_4.plane
    FROM table_1
    JOIN table_2
        ON table_1.username=table_2.username
    JOIN table_3
        ON table_1.username=table_3.username
    JOIN table_4
        ON table_1.username=table_4.username
    WHERE table_1.username = 'user_2'";

Answer 1

look into an OUTER JOIN. It allows records on one side, but optional on the other (left outer / right outer). If no record found on the "other" table, its column will result in being NULL in the result set, so careful on that

Answer 2

Typically in such a case you would look at a LEFT JOIN.

Something like

SELECT table_1.username, table_2.car, table_3.bike, table_4.plane 
    FROM table_1 
    JOIN table_2 
        ON table_1.username=table_2.username 
    JOIN table_3 
        ON table_1.username=table_3.username 
    LEFT JOIN table_4 
        ON table_1.username=table_4.username 
    WHERE table_1.username = 'user_2'

This might also be the case then in any of the other tables, so even this might be more helpfull

SELECT table_1.username, table_2.car, table_3.bike, table_4.plane 
    FROM table_1 
    LEFT JOIN table_2 
        ON table_1.username=table_2.username 
    LEFT JOIN table_3 
        ON table_1.username=table_3.username 
    LEFT JOIN table_4 
        ON table_1.username=table_4.username 
    WHERE table_1.username = 'user_2'

Answer 3

I think Jeff's article on JOINs should help you out.