Home > Enterprise >  mysql select values as column from another table
mysql select values as column from another table

Time:04-02

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

  • Related