MYSQL JOIN with IN clause

Question

I have the following which returns specific posts from a table. As expected it gives 11 rows.

-- SQL gives 11 rows
select p.ID from wp_posts p where p.ID IN (2596, 2597, 2598, 2600, 2849, 2928, 2851, 2852, 3011, 2854, 2855);

I have a second table 'wp_raceresult' which records a person's race results. The person only has 7 results in this table. I want to get the details of all race results in this set of races for a specific runner, ie NULL for the each race where there is no result. The SQL i have atm only results the matching rows.

-- SQL only gives 7 rows
select p.ID,rr.leaguepoints from wp_posts p
join wp_raceresult rr on p.id=rr.race
where p.ID IN (2596, 2597, 2598, 2600, 2849, 2928, 2851, 2852, 3011, 2854, 2855)
and runner=7

I've tried using FULL OUTER, LEFT, RIGHT clauses and a UNION but without success. Any idea?

score 2 · Accepted Answer · edited May 23 '17 at 12:30

Since you have mentioned that you already tried LEFT JOIN and the result you got is not the one you expect, it is because you are querying for runner = 7 in the final result. Move the statement on the ON clause and it will work.

SELECT  p.ID,
        rr.leaguepoints
FROM    wp_posts p
        LEFT JOIN wp_raceresult rr
            ON p.id = rr.race AND 
                rr.runner = 7                             -- <<=== move it here
WHERE p.ID IN (2596, 2597, 2598, 2600, 2849, 2928, 2851, 2852, 3011, 2854, 2855)

Additional info:

Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?

score -1 · Answer 2 · answered Sep 26 '13 at 16:18

If you need all the record from one table, and the data matching in the reference table you can try with the LEFT JOIN

    select 
    p.ID,
    rr.leaguepoints 
    from wp_posts p
    LEFT join wp_raceresult rr on p.id=rr.race
    where p.ID IN (2596, 2597, 2598, 2600, 2849, 2928, 2851, 2852, 3011, 2854, 2855)
    and runner=7

MYSQL JOIN with IN clause

2 Answers2