SQL result should show all table one data

Question

I have two tables as shown below:

table_one

teamid     teamname   description
   1           x         abcd
   2           y         dcba
   3           z         sadf

table_two

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

Expected result table

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?

`LEFT JOIN` will be your friend. The problem is, you've added a condition, `WHERE stageid = 1`. This will return only 2 records. — vaso123, Mar 16 '16 at 09:46
@lolka_bolka I already tried that as well. It isn't giving me the third row as I shown in the expected result table. — Mr_Green, Mar 16 '16 at 09:46

score 3 · Accepted Answer · edited May 23 '17 at 11:45

3

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

edited May 23 '17 at 11:45

Community

1
1

answered Mar 16 '16 at 09:46

Nergal

985
1
14
29

Somethings I really easy which I think as complicated. Thanks :) – Mr_Green Mar 16 '16 at 09:49
1

If you move `stageid = 1` to the `ON` clause you can skip the `OR stageid IS NULL` part. – jarlh Mar 16 '16 at 09:53

score 2 · Answer 2 · answered Mar 16 '16 at 09:54

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;

SQL result should show all table one data

table_one

table_two

Expected result table

2 Answers2