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