Returning data from multiple tables in one query even if secondary tables do not apply

Question

I have an issue getting data from three tables, which I want to return using one query. I've done this before using a query something like this one:

$query = mysql_query("SELECT 
    maintable.`id`,
    maintable.`somedata`,
    maintable.`subtable1_id`,
    subtable1.`somedata`,
    subtable1.`subtable2_id`,
    subtable2.`somedata`
FROM
    `maintable` maintable,
    `subtable1` subtable1,
    `subtable2` subtable2
WHERE
    maintable.`somedata` = 'some_search' AND
    subtable1.`id` = maintable.`subtable1_id` AND
    subtable2.`id` = subtable1.`subtable2_id`
")or die(mysql_error());

The problem this time is that the extra details might not actually apply. I need to return all results that match some_search in maintable, even if there is no subtable1_id specified.

I need something that will go along the lines of

WHERE
     maintable.`somedata` = 'some_search'
AND IF maintable.`subtable1_id` IS NOT NULL (
     WHERE subtable1.`id` = maintable.`subtable1_id` AND
     subtable2.`id` = subtable1.`subtable2_id`
)

As you will probably guess, I am not an advanced mysql user! Try as I might, I cannot get the syntax right, and I have had no luck searching for solutions on the web.

Any help much appreciated!

Answer 1

If I got this right, you need to use MySQL LEFT JOIN. Try this:

SELECT 
    m.id, 
    m.somedata, 
    m.subtable1_id,
    s1.somedata,
    s1.subtable2_id,
    s2.somedata
FROM
    maintable m
LEFT JOIN
    subtable1 s1
ON
    m.subtable1_id = s1.id
LEFT JOIN
    subtable2 s2
ON
    s1.subtable2_id = s2.id
WHERE
    m.somedata = 'some search'

This will return the data of maintable even if there's no equivalent data in subtable1 or 2 OR data of maintable and subtable1 if there's no equivalent data in subtable2.

Answer 2

How about this:

WHERE
     maintable.`somedata` = 'some_search'
AND (maintable.`subtable1_id` IS NULL OR (
     subtable1.`id` = maintable.`subtable1_id` AND
     subtable2.`id` = subtable1.`subtable2_id` )
)

Keep in mind that this will result in a cross product of subtable1 and subtable2 with maintable when subtable1_id is NULL.

Answer 3

It seems like the basic distinction you're looking for is between an INNER JOIN and a LEFT JOIN in MySQL.

An INNER JOIN will require a reference between the two tables. There must be a match on both sides for the row to be returned.

A LEFT JOIN will return matches in both rows, like an INNER, but it will also returns rows from your primary table even if no rows match in your secondary tables -- their fields will be NULL.

You can find example syntax in the docs.