I have two tables as shown below:
teamid teamname description
1 x abcd
2 y dcba
3 z sadf
stageid teamid responses score
1 1 r1 20
1 2 r2 30
2 1 r4 20
2 2 r5 20
2 3 r6 20
I am trying to join the above two tables based on stageid number which I have. So, I am tried the following:
SELECT t1.teamid, t1.teamname, t2.responses, t2.score
FROM table_one as t1
JOIN table_two as t2 ON t1.teamid = t2.teamid
WHERE stageid = 1
Which is giving me following result (I tried all combinations of left, right, inner, outer joins):
teamid teamname responses score
1 x r1 20
2 y r2 30
teamid teamname responses score
1 x r1 20
2 y r2 30
3 z NULL 0
As you can see in the above expected table, I want all the rows of the table_one and from table_two if the data isn't present, I need NULL or 0 as values.
How to do this?
Try this:
SELECT t1.teamid, t1.teamname, t2.responses, t2.score
FROM table_one as t1
LEFT JOIN table_two as t2 ON t1.teamid = t2.teamid
WHERE stageid = 1 OR stageid IS NULL
By default when you use left join and there is nothing to join the fields gonna contain NULL, so you have to add NULL not just a specific stageid.
Or as others menthioned you can set the stageid like this:
SELECT t1.teamid, t1.teamname, t2.responses, t2.score
FROM table_one as t1
LEFT JOIN table_two as t2 ON t1.teamid = t2.teamid AND stageid = 1
WHERE 1
In this query you use the ON tag of the join to set the stageid and you get the same result. (WHERE 1 is not necessary)
Check JOINs: https://stackoverflow.com/a/6188334/2231168
If you want to retrieve the non-matching record also then use LEFT JOIN instead of INNER JOIN.
Query
select t1.teamid, t1.teamname, t2.responses, coalesce(t2.score, 0) as score
from table_one t1
left join table_two t2
on t1.teamid = t2.teamid
and t2.stageid = 1;