SQL结果应该显示所有表一个数据

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?

Answer 1

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