Q:

SQL QUERY using LEFT JOIN and CASE Statement

here is an example of my two table.

PROBLEM:
How can I create SQL Query using left join?
HERE IS THE SCENARIO
As I've said earlier, I have two table (TABLE1 and TABLE2),
I tried to use left join so that i can combine both UserID
in one table
so here is the code
select * from table1 a left join table2 on a.userid = b.userid

so two tables are now combined.
what i need to do is this: 
if the status is all complete then 'complete' 
then if status contains complete and incomplete then 'incomplete' 
else 'no status' 
it should be look like this.

NOTE: 
since UserID = 1 (table1) contains complete and incomplete status (table2) 
then it display 'incomplete' (new column) 

since UserID = 4 (table1) contains all complete status (table 2) 
then it display 'completed' (new column)
-----------------------------------
WHAT IF I CHANGE THE STATUS TO INTEGER?

same procedure. thanks 

A:

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

SQLFiddle Demo

UPDATE 1
the only thing you'll change is the 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;

SQLFiddle Demo

A:

Easy, but tricky solution :
as INCOMPLETE is greater (for a 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's better solution as well)

A:

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

