CODEWITHSUNDEEP
mysqlsqljoin
vasya
vasya

Reputation: 35

Joining three tables mysql

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.

Upvotes: 0

Views: 64

Answers (6)

Sijo George Oommen
Sijo George Oommen

Reputation: 9

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.

Upvotes: 0

isaace
isaace

Reputation: 3429

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  
)

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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)
);

Upvotes: 1

cdaiga
cdaiga

Reputation: 4939

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;

Upvotes: 0

Hunter McMillen
Hunter McMillen

Reputation: 61515

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

Upvotes: 0

Hex
Hex

Reputation: 145

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

Upvotes: 0

Related Questions