0

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 ?

Andy Su
  • 131
  • 7
  • You need to join using `JSON_SEARCH`, and then pivot the results. See https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns for how to pivot – Barmar Apr 02 '22 at 03:14

2 Answers2

0
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!
mahy
  • 1
  • 1
    Nice answer, but usually is better to not provide "only code" answers and trying to explain what it does – Francisco Puga Apr 02 '22 at 10:49
  • If the version of MySQL is important, you can check with `select @@version` and put the version here. Why do you mention JSON type? Is `a.label` a stringified JSON? Also, the `JOIN ON` condition `FIND_IN_SET(b.idB,a.label)` is probably not what you want. This must be a condition for the engine to know when to match rows from both tables e.g. `ON a.idA = b.idB`. It would be helpful if you add examples of the content for tables `a` & `b` and what results you expect. – tiomno Apr 02 '22 at 10:52
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – tiomno Apr 02 '22 at 10:52
  • It's good when number of objects are limited but it's not, any way to improve ? – Andy Su Apr 08 '22 at 04:26
-1

Try selecting from both tables together since you can't join them - select t1.*, t2.* from table1 t1, table2 t2

Asad Awadia
  • 1,417
  • 2
  • 9
  • 15