Given the following input tables:
table_1
| tid | name | pic |
|---|---|---|
| 1 | name1 | XXX |
| 2 | name2 | ZZZ |
| 3 | name3 | YYY |
table_2
| fid | fname | tid | desc |
|---|---|---|---|
| 1 | abcd1 | 1 | File1 |
| 2 | abcd2 | 1 | File2 |
| 3 | xyze3 | 2 | File1 |
I want to show "table_2.desc" as a field and the value is "yes" or "no" based on if "tid" have "desc" with data "File1" (so "File1" will be "Yes").
Expected Output:
| tid | name | pic | File1 | File2 |
|---|---|---|---|---|
| 1 | name1 | XXX | Yes | Yes |
| 2 | name2 | ZZZ | Yes | No |
| 3 | name3 | YYY | No | No |
CodePudding user response：
Using a left join with aggregation we can try:
SELECT t1.tid, t1.name, t1.pic,
CASE WHEN SUM(t2.`desc` = 'File1') > 0 THEN 'Yes' ELSE 'No' END AS File1,
CASE WHEN SUM(t2.`desc` = 'File2') > 0 THEN 'Yes' ELSE 'No' END AS File2
FROM table_1 t1
LEFT JOIN table_2 t2
ON t2.tid = t1.tid
GROUP BY t1.tid, t1.name, t1.pic;