Joining three tables mysql

Question

I have three tables:

Houses Table

id title description -------------------------- 1 Big House Very big house 2 Small House Very small house

Rooms Table

id title room_status -------------------------- 1 Green room 24 2 Yellow room 25 3 Blue room 24

Houses_Rooms Table

id house_id room_id ------------------------- 1 1 1 2 2 3 3 1 2

I have room status 24, for example.

I need get all houses from Houses Table that contains rooms with room_status = 24. But, if house has rooms with different room_status we do not need to choose this house.

I build some query and i use WHERE house.id IN (SELECT ...). It works, but returns houses with same room_status, because IN.

So you want all houses that have a room with status = 24, unless they also have a status <> 24? i.e. in the scenario above you expect only house id 2 to be returned — Hunter McMillen
– Hunter McMillen, Commented Apr 19, 2018 at 14:49
If I understand correctly, a given room can only belong to 1 specific House. Therefore the satus of a room is a property of the room, and you only need 2 tables: Houses and Rooms (the later with a HouseId field). In other words, your schema sucks :-) — Patrick Honorez
– Patrick Honorez, Commented Apr 19, 2018 at 15:02
Quote @Thorsten Kettner:You seem to want houses where all rooms have status 24. — Ibrahim Mohamed
– Ibrahim Mohamed, Commented Apr 19, 2018 at 15:03

Thorsten Kettner · Accepted Answer · 2018-04-19 14:59:19Z

You seem to want houses where all rooms have status 24.

WHERE house.id IN (SELECT ...) is a good idea. Now you need a subquery that only contains houses with only status-24 rooms.

select * from houses where id in ( select hr.house_id from houses_rooms hr join rooms r on r.id = hr.room_id group by hr.house_id having min(r.room_status) = 24 and max(r.room_status) = 24 );

However, as it is unlikely that a house has no rooms at all, we can simply exclude houses with non-status-24 rooms instead:

select * from houses where id not in ( select house_id from houses_rooms where room_id in (select id from rooms where status <> 24) );

I don't see any array here. You want an information on the rooms per house, so you group by house and check the accumulated room info. This is probably the best way to solve this, provided we need to check that a house has at least one status-24 room. Otherwise my other query is much better. An alternative to the aggregation (and the straight-forward Approach at that) would be one IN query for status-24 houses and one NOT IN query for non-status-24 houses.

Hex · Accepted Answer · 2018-04-19 14:53:45Z

You should use an INNER JOIN instead of a sub-query for this kind of query.

SELECT HR.ID ,H.Title ,H.Description ,R.Title FROM #House_Room HR INNER JOIN #Houses H on HR.HouseID = h.ID INNER JOIN #Rooms R on HR.RoomID = r.ID WHERE R.RoomStatus = 24

Returns:

1 | Big House | Very big house | Green room 2 | Small House | Very small house | Blue room

Hunter McMillen · Accepted Answer · 2018-04-19 14:55:53Z

The below query selects all houses that have rooms with room_status = 24, but excludes houses that also have a room_status <> 24. It can probably be further optimized but my SQL is a little rusty:

select h.* from houses h join houses_rooms hr on h.id = hr.house_id join rooms r on hr.room_id = r.id where r.room_status = 24 and h.id not in ( select h.id from houses h join houses_rooms hr on h.id = hr.house_id join rooms r on hr.room_id = r.id where r.room_status <> 24 );

SQLFiddle Example

cdaiga · Accepted Answer · 2018-04-19 14:56:57Z

Try this:

SELECT A.* FROM Houses A JOIN Houses_Rooms B ON A.id=B.house_id JOIN (SELECT * FROM Rooms WHERE room_status=24) C ON C.id=B.room_id;

isaace · Accepted Answer · 2018-04-19 14:59:58Z

This should work because it uses JOINS to get those that have a room with room_status = 24 and then uses NOT IN to rule out those that have other room statuses

SELECT * FROM Houses_Rooms hr JOIN Houses h on hr.house_id = h.id JOIN Rooms r on hr.room_id = r.id WHERE r.room_status = 24 AND h.id NOT IN( SELECT h.house_id FROM Houses_Rooms h JOIN Rooms r on h.room_id = r.id WHERE r.room_status <> 24 )

Sijo George Oommen · Accepted Answer · 2018-04-19 15:01:09Z

Select houses.title as 'house name' From houses, rooms,houses_rooms where houses.id=houses_rooms.house_id and rooms.id=houses_rooms.room_id and rooms.room_status=24

try this..am not 100% sure..i usually dont use joins.

Collectives™ on Stack Overflow

Joining three tables mysql

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Related