Query for mysql

July 3, 2022

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

Thanks

>Solution :

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;