MySQL Join syntax for one to many relationship

Question

I have a situation where I have one table of titles (t1) and another table with multiple links that reference these titles (t2) in a one to many relationship.

What I want is the full list of titles returned with a flag that indicates if there is a specific link associated with it.

Left Join and Group By:

SELECT
    t1.id
    , t1.title
    , t2.link_id AS refId
FROM
    t1
    LEFT JOIN t2
        ON (t1.id = t2.title_id)
GROUP BY t1.id;

This is close as it gives me either the first link_id or NULL in the refId column.

Now, how do I constrain the results if I have a specific link_id rather than allowing t2 run through the whole data set?

If I add a WHERE clause, for example:

WHERE t2.link_id = 123

I only get the few records where the link_id matches but I still need the full set of titles returned with NULL in the refId column unless link_id = 123.

Hope someone can help

Solution 1

Instead of in the WHERE clause, put your criteria in the LEFT JOIN clause:

SELECT
    t1.id
    , t1.title
    , t2.link_id AS refId
FROM
    t1
    LEFT JOIN t2
        ON t1.id = t2.title_id AND t2.link_id = 123
GROUP BY t1.id;

Solution 2

Put it in the join condition for the second table

SELECT t1.id, t1.title, t2.link_id as refId
FROM t1
LEFT JOIN t2 ON t1 = t2.title_id AND t2.link_id = 123
GROUP BY t1.id;

Comments

[Sam Stoelinga]

Shouldnt it be: ON t1.id = t2.title_id ? Or will it automatically take the pk when specifying the table?