Menu
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?
602
SQL handles queries across more than one table through the use of JOINs. JOINs are clauses in SQL statements that link two tables together, usually based on the keys that define the relationship between those two tables.
You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
First, specify the main table that appears in the FROM clause ( t1 ). Second, specify the table that will be joined with the main table, which appears in the INNER JOIN clause ( t2 , t3 ,…). Third, specify a join condition after the ON keyword of the INNER JOIN clause.
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:
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
