0

I have two tables as follows. (Table A and Table B). Table B has a field that contains multiple IDs from table A but comma separated. They are guaranteed to exists in Table A.

Table A
FIELD1  FIELD2
1        CAR
2        BUS
3        TRUCK
.
.

Table B FIELD2 : "1,3".

What I want is for each record in B (From filter condition)

FIELD1   FIELD2      FIELD3 
B.ID     CAR,TRUCK   something....

I want to pull FIELD2 from table A where IDs are matched for each field in Table B (FIELD2). What's the best way I could achieve this?

This is what I tried, but it does not give what I want.

SELECT 
GROUP_CONCAT(A.FIELD2) AS VEHICALS
FROM B 
LEFT JOIN A ON A.FIELD1 IN (B.FIELD2)
WHERE B.FIELD_X > 20;

Any help is appreciated.

PCG
  • 65
  • 8
  • This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Mar 08 '20 at 19:29

1 Answers1

1

Use FIND_IN_SET() and GROUP BY B.ID, B.FIELD2

SELECT B.ID, GROUP_CONCAT(A.FIELD2 ORDER BY A.FIELD1) AS VEHICALS
FROM B INNER JOIN A 
ON FIND_IN_SET(A.FIELD1, B.FIELD2)
WHERE B.FIELD_X > 20
GROUP BY B.ID, B.FIELD2

Since the ids are guaranteed to exist in Table A there is no need for a LEFT join, use an INNER join.
See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76