Let's assume I have those datasets:

Table A:

id (int)	value (varchar)	b_ids(varchar)
1	a value	1
2	another value	2,3

Table B:

id (int)	value (varchar)
1	a value
2	another value
3	another another value

The reason I have to use b_ids here is because the B rows have to be inserted before the A rows

I am trying to SELECT rows from Table A and the corresponding values from Table B in one single query, and make that query a view for filtering purposes

My attemps so far only gave me back the A rows the first value from the related B rows:

SELECT * FROM A
LEFT JOIN B ON B.id IN (A.b_ids);

And I obtained something like this:

id	value	b_ids	id	value
1	a value	1	1	a value
2	another value	2,3	2	another value

I have tried other joins (INNER JOIN, RIGHT JOIN, CROSS JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN), with the same result

I obviously am still missing something in the joins department if my endeavor is even possible to do in one single SELECT

Is there a way to return the values of Table B as an array of rows in this query?

Even if the result below is the output, I can work with it:

id	value	b_ids	id	value
1	a value	1	1	a value
2	another value	2,3	2	another value
2	another value	2,3	3	another another value

Note: I have chosen Table A as the first table here because the real case involves joins with other tables

CodePudding user response：

you should change tables schema if there is one to many relationship between A(1)---(n)B tables should be like this:

Table A:

id (int)	value (varchar)
1	a value
2	another value

Table B:

id (int)	value (varchar)	a_ids(varchar)
1	a value	1
2	another value	2
3	another another value	2

so now you can define tables relationship or fetch data as you need in single query easily

** if tables has many to many relation you need a pivot table.