CODEWITHSUNDEEP
mysql
emeraldjava
emeraldjava

Reputation: 11202

MYSQL JOIN with IN clause

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?

Upvotes: 11

Views: 21782

Answers (2)

John Woo
John Woo

Reputation: 263723

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:

Upvotes: 3

Sashi Kant
Sashi Kant

Reputation: 13465

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

Upvotes: -1

Related Questions