G.Smith G.Smith - 1 month ago 3x
MySQL Question

Sql query when one of tables could have more results

I'm trying to do a query to show images attached to a "room" table but one room may have more images than another. I have 3 tables :

  • "answer" table - this holds all form info stored from the app.

  • "room" table - this has a "form id"(the id of the answer table) and some room details

  • "roomimage" table - this has a "room" id and a url for the images, so there could be 10 pictured for one room and 5 for another, for the same form.

My question is, is it possible to get all these details in one query? I know the fact that the last table can have multiple columns may be an issue? I tried the below :

answer.id, room.name, room.description, roomimage.image_url
inner join
room on room.form_id = answer.id
inner join
roomimage on roomimage.room_id = room.id
answer.id = 3

but if a form has a room that has 2 images, it is returning 2 rows, I want it to be 1 row with dynamic columns like on the attachment (bottom image)



I would do it by limiting the number of maximum results you want to get:

select answer.id,room.name,room.description,
    (select roomimage1.image_url FROM roomimage AS roomimage1 on roomimage1.room_id = room.id LIMIT 0, 1) AS image_url_1,
    (select roomimage2.image_url FROM roomimage AS roomimage2 on roomimage2.room_id = room.id LIMIT 1, 1) AS image_url_2,
    (select roomimage3.image_url FROM roomimage AS roomimage3 on roomimage3.room_id = room.id LIMIT 2, 1) AS image_url_3,
    (select roomimage4.image_url FROM roomimage AS roomimage4 on roomimage4.room_id = room.id LIMIT 3, 1) AS image_url_4,
    (select roomimage5.image_url FROM roomimage AS roomimage5 on roomimage5.room_id = room.id LIMIT 4, 1) AS image_url_5
from answer
inner join room on room.form_id = answer.id
where answer.id = 3

It's not pretty but by doing this you get up to 5 results (NULL when not present)

NB: This was the answer in full SQL, if you can access the data with a scripting language you can process manually the results of your query.