-1

I have 2 tables, rooms and images, and I want to show rows with all the rooms I have for that specific hotel and also show me the each images from the rooms, but the problems is when I use JOIN SELECT is show me 8 rows from all the queries from the second tables, which means is showing me 3 times(i have 3 images for this room) the single room and 5 times the double rooms (i have 5 images for this room) so is showing me 8 rows, and I want only 2 rows for each rooms with each images - 3 and 5 

table 1 - rooms
id, id_hotel, type_room
1, 1, single
2, 2, double

table 2 - images
id, id_rooms, url
1, 1, image-A.jpg
2, 1, image-b.jpg
3, 1, image C.jpg
4, 2, image D.jpg
5, 2, IMAGE E.jpg
6, 2, IMAGE f.jpg
7, 2, IMAGE g.jpg
8, 2, IMAGE h.jpg

How I can Do?? I tried also RIGHT JOIN and LEFT JOIN ... other solution?

Alcatraz007
  • 93
  • 8
  • What are your expected results? If you want to put all the images in a single column, use `group_concat`... – sgeddes Jun 12 '18 at 20:37
  • 1
    Show us the query you try, and the result you want. `I want only 2 rows` is very vague on what is the expected result – Juan Carlos Oropeza Jun 12 '18 at 20:37
  • I want to show me 2 big rows for each ROOMS and in that rows to show me a carousel for image for the specifically IMAGEs, so show me a room single with 3 image in a carousel and double room rows with 5 images, but is showing me 8 rows for each image... – Alcatraz007 Jun 12 '18 at 20:41
  • Its called pivot table. This has all the info you'll need: https://stackoverflow.com/q/7674786/1543677 – pkExec Jun 12 '18 at 20:43
  • 1
    Again show us on the question what result you want. We are not mind readers and sql doesn't know what a carousel does. So you need to explain what structure you need for your carousel to work – Juan Carlos Oropeza Jun 12 '18 at 20:46
  • can you show me a exemple with my situation??, i watched at pivot table but to be honest I did not understand nothing – Alcatraz007 Jun 12 '18 at 20:46
  • Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – pkExec Jun 12 '18 at 20:46
  • same row `

    echo type_room / single

    ` something like this i want to show me a row, but the problem is the row give me something like this `

    echo type_room / single

    ` row 1     – Alcatraz007 Jun 12 '18 at 20:50
  • as sgeddes said, you need `GROUP_CONCAT` – Juan Carlos Oropeza Jun 12 '18 at 20:52
  • I see no query here – Strawberry Jun 12 '18 at 23:54

1 Answers1

0

You want something like this?

SQL DEMO

SELECT `id_rooms`, GROUP_CONCAT( CONCAT('<img src="',`url`,'>') SEPARATOR '') as i
FROM images
GROUP BY `id_rooms`;

OUTPUT

enter image description here

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • no I want something like this from the 2 tables `` +----------+---------+----------------------------+ | rows | id_rooms | url | +----------+---------+----------------------------+ | 1 | single|
    | +----------+---------+----------------------------+ | 2 | double|
    | +----------+---------+-------------------+``     – Alcatraz007 Jun 13 '18 at 16:39