It's hard to give a title to this question but much easier to understand by showing the structure of tables:
There are 2 tables below:
Table A:
idA(int) | label(json) |
---|---|
1001 | [1, 3] |
1002 | [3, 2] |
Table B:
idB(int) | name(string) |
---|---|
1 | apple |
2 | orange |
3 | strawberry |
Require outputs:
idA | apple | orange | strawberry |
---|---|---|---|
1001 | 1 | 0 | 1 |
1002 | 0 | 1 | 1 |
how to use mysql query to get this ?
CodePudding user response:
select
a.idA,
SUM(CASE b.name WHEN 'apple' THEN 1 ELSE 0 END) apple,
SUM(CASE b.name WHEN 'orange' THEN 1 ELSE 0 END) orange,
SUM(CASE b.name WHEN 'strawberry' THEN 1 ELSE 0 END) strawberry
from a
left join b on FIND_IN_SET(b.idB,a.label)
group by a.idA
- My mysql version is low and there is no json type. I hope this will help you!
CodePudding user response:
Try selecting from both tables together since you can't join them - select t1.*, t2.* from table1 t1, table2 t2