How to add multiple condition in a case statement in Mysql?-CodePudding

I have two tables.

Table1; includes uid and age.

Table2; includes uid, age, and sex.

I want to make a new dataset and combine all the records, then add two new columns to flag ids that visit the shop(Visit) and another column(CM) to display those that "saw the ad. and went to the shop" and records that "saw the ad. but didn’t go to the shop".

I tried the code in below however I feel the result is not reasonable.

Please advise.

select t1.uid, t1.age,
    
    case when t2.uid is not null then 'yes' else 'no' end as Visit,
    case when t1.uid is not null and t2.uid is not null then 'yes' 
         when t1.uid is not null and t2.uid is null then 'no' end as CM
        

from t1
full outer join t2
on t1.uid=t2.uid

CodePudding user response：

MySQL does not support a FULL OUTER JOIN, you have to do a combination of LEFT JOIN, UNION, and RIGHT JOIN as a workaround.

As I've learned recently, it's been requested in MySQL since 2006 but has not been a priority to implement (Reference).

SELECT c.UID, c.Age, c.Sex, c.Visit, c.CM
  FROM 
(SELECT 
   COALESCE(a.UID, b.UID) AS UID, 
   COALESCE(a.Age, b.Age) AS Age,
   COALESCE(b.Sex, 'N/A') AS Sex,
   IF(b.UID IS NOT NULL, 'Yes', 'No') AS Visit,
   CASE WHEN a.UID IS NOT NULL AND b.UID IS NOT NULL THEN 'Yes'
        ELSE 'No'
   END AS CM
 FROM Table1 a
 LEFT JOIN Table2 b ON a.UID = b.UID 
   UNION
 SELECT 
   COALESCE(a.UID, b.UID) AS UID, 
   COALESCE(a.Age, b.Age) AS Age,
   COALESCE(b.Sex, 'N/A') AS Sex,
   IF(b.UID IS NOT NULL, 'Yes', 'No') AS Visit,
   CASE WHEN a.UID IS NOT NULL AND b.UID IS NOT NULL THEN 'Yes'
        ELSE 'No'
   END AS CM
 FROM Table1 a
 RIGHT JOIN Table2 b ON a.UID = b.UID) c
ORDER BY c.UID ASC

Result:

| UID | Age | Sex | Visit | CM  |
|-----|-----|-----|-------|-----|
| 1   | 25  | M   | Yes   | Yes |
| 2   | 44  | N/A | No    | No  |
| 3   | 52  | F   | Yes   | Yes |
| 4   | 30  | N/A | No    | No  |
| 5   | 38  | M   | Yes   | Yes |
| 6   | 63  | F   | Yes   | No  |
| 7   | 49  | N/A | No    | No  |

Demo here