Clever Geek Handbook

SQL QUERY using LEFT JOIN and CASE Statement

here is an example of my two tables.

enter image description here

Problem: How do I create a SQL query using a left join?

HERE IS THE SCENARIO

As I said before, I have two tables (TABLE1 and TABLE2), I tried to use a left join to combine both UserIDs in one table

so here is the code

select * from table1 a left join table2 on a.userid = b.userid

so the two tables are now merged.

what I need to do:
if the status is fully completed , then 'complete'
then if the status contains complete and incomplete , then "incomplete"
else 'no status'

it should look like this.

enter image description here

NOTE :
since UserID = 1 (table1) contains full and incomplete status (table2)
it displays "incomplete" (new column)


since UserID = 4 (table1) contains all full status (table 2)
then it displays "completed" (new column )

-----------------------------------

WHAT IF I CHANGE THE STATUS OF INTEGERA?

enter image description here

the same procedure. thank

+3


source to share


3 answers 


SELECT  a.*, 
        CASE WHEN b.totalCount = 1 AND b.totalINC = 0 THEN 'Complete'
             WHEN totalCount IS NULL THEN ''
             ELSE 'Incomplete'
        END STatus
FROM    table1 a
        LEFT JOIN
        (
            SELECT  UserID, 
                    COUNT(DISTINCT STATUS) totalCount,
                    SUM(CASE WHEN status = 'Incomplete' THEN 1 ELSE 0 END) totalINC
            FROM table2
            GROUP BY UserID
        ) b ON a.UserID = b.UserID

UPDATE 1



the only thing you change is CASE 

SELECT  a.*, 
        CASE WHEN b.totalCount = 1 AND b.totalINC = 0 THEN 'Complete'
             WHEN totalCount IS NULL THEN ''
             ELSE 'Incomplete'
        END STatus
FROM    table1 a
        LEFT JOIN
        (
            SELECT  UserID, 
                    COUNT(DISTINCT STATUS) totalCount,
                    SUM(CASE WHEN status <> 100 THEN 1 ELSE 0 END) totalINC
            FROM table2
            GROUP BY UserID
        ) b ON a.UserID = b.UserID;
+6


source


Simple but tricky solution:

since INCOMPLETE is larger (for db) than COMPLETE, you can simply do



SELECT a.UserID, 
  LOWER(COALESCE(MAX(b.status) , 'NO STATUS'))
  FROM table1 a 
 LEFT JOIN table2 b on a.userid = b.userid
 GROUP BY a.UserID

SqlFiddle (with Andomar best solution)

+3


source 


select  a.UserID
,       case
        when sum(case when b.status = 'Incomplete' then 1 end) > 0 
            then 'Incomplete' 
        when sum(case when b.status = 'Complete' then 1 end) > 0 
            then 'Complete' 
        else 'No Status' 
        end
from    table1 a 
left join 
        table2 b
on      a.userid = b.userid
group by
        a.UserID
+3


source






More articles:


All Articles