Mr_Green Mr_Green - 7 months ago 8
SQL Question

Join two tables and show null in columns if column is not present

I have two tables as follows.

stages:



stageid stagename is_corrected
1 abc 1
2 xyz 1
3 aaa 0
4 bbb 1


responses:



stageid teamid diffscore
1 1 10
1 2 12
1 3 15
2 1 12
2 2 13
2 3 16
2 4 14


I am trying to join them and show a joined table where
is_corrected
= 1. Here is the query I tried:

Query:



SELECT
t1.stagename,
t2.diffscore
FROM
stages t1
LEFT OUTER JOIN (
SELECT
diffscore,
teamid,
stageid
FROM
responses as t2
) as t2 ON t2.stageid = t1.stageid
OR t1.stageid = NULL
WHERE
t1.is_corrected = 1
AND (
t2.teamid = 4
OR t2.teamid = NULL
)


Expected Result:



stagename diffscore
abc NULL
xyz 14
bbb NULL


Output:



stagename diffscore
xyz 14

Answer

Try this:

SELECT 
    s.stageid, 
    s.stagename, 
    IFNULL(
        (
            SELECT 
                diffscore 
            FROM 
                responses 
            WHERE 
                s.stageid = stageid 
                AND (
                    teamid = 1
                    OR teamid IS NULL
                )
            LIMIT 1
        ), 
        0
    ) AS diffscore 
FROM 
    stages s 
WHERE 
    is_corrected = 1
Comments