Join multiple rows of on table into array for joined table

Question

I have a table called idcards with a foreign key on the id of the table reservations. Then I have a table photos that has a foreign key on the id in table idcards.

I want to select all from idcards where the reservation id is a certain value, plus the photos that are attached to this idcard.

This is what I have so far, but it obviously selects only one photo per idcard:

SELECT i.*, p.photo 
FROM idcards AS i 
INNER JOIN photos AS p 
    ON (i.id=p.iId) 
WHERE i.resId = 1

How can I make it so the idcards that I get have an array with all the photos ?

What does "array" have to do with your question? MySQL doesn't support arrays. — Gordon Linoff
– Gordon Linoff, Commented Feb 12, 2021 at 13:02
'obviously selects only one photo per idcard:' - not obvious to me , I would have said this results in 1 row per photo..Please add sample data and expected outcome as text to the question. — P.Salmon
– P.Salmon, Commented Feb 12, 2021 at 13:08
I suspect you are looking for group_concat dev.mysql.com/doc/refman/8.0/en/… — P.Salmon
– P.Salmon, Commented Feb 12, 2021 at 14:07

César Reyes · Accepted Answer · 2021-02-12 14:42:41Z

2

If you want only the field photo from the table photos, you can group them in an array, associating it to the idcard, with a query like the following:

SELECT i.*, JSON_ARRAYAGG(p.photo) FROM idcards AS i LEFT JOIN photos AS p ON (i.id=p.iId) WHERE i.resId = 1 GROUP BY i.id;

Or the following, to return the photos in a same field, separed by a comma:

SELECT i.*, GROUP_CONCAT(p.photo) FROM idcards AS i LEFT JOIN photos AS p ON (i.id=p.iId) WHERE i.resId = 1 GROUP BY i.id;

answered Feb 12, 2021 at 14:42

César Reyes

813 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Join multiple rows of on table into array for joined table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related