CODEWITHSUNDEEP
mysqljoin
Xtreme
Xtreme

Reputation: 1601

How do I select rows from a table if I know value in another table but not ID?

I have this table structure (I made it myself, it is free to say if you think it is bad and give better example) Tables Several images can be found in one room which is mapped in the table image_room. Image_room looks like

image_id | room_id
    1 | 1
    2 | 1
    3 | 1
    4 | 2
    5 | 3
    6 | 4

Room with id 1 contains three images

I know guid from room table. How do I select all the image_name in a room based on the GUID number I know in MySQL?

Eg I know the guid XXX. With a select I want to find out that XXX has room_id 1 in the room table. My map table says that images 1, 2 and 3 are in that room.

Upvotes: 0

Views: 28

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

The normal way to do this would use an explicit join:

select i.image_name
from room r inner join
     image_room ir
     on r.room_id = ir.room_id inner join
     image i
     on ir.image_id = i.image_id
where r.guid = @GUID;

You should declare your id columns to be primary keys, probably auto-incremented primary keys, for better performance.

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 1549

try below:

select image_name from image where image_id in (select image_id from image_room where room_id in (select room_id from room where room_guid = 'xxx'))

Upvotes: 0

Related Questions