Sanooj T Sanooj T - 5 months ago 15
PHP Question

multiple subquery need to change as join query

Hi all I need a solution for this question here is my query look like it works.But takes too much time because of sub queries.Give an alternate query to this query

SELECT *
FROM `room_types`
WHERE id
IN (SELECT capacity
FROM rooms
WHERE id
IN (
SELECT DISTINCT room_id
FROM `reservations`
WHERE DATE(
START ) >= '2016-01-10'
AND DATE(
END ) <= '2016-01-15'
AND STATUS = 'CheckedOut'
AND id
IN (
SELECT op_no
FROM `bills`
WHERE billed = 'Yes'
)
)
)

Answer

So.. First try without Database Schema !

   SELECT *
    FROM room_types rt
    JOIN rooms r ON rt.room_id = r.id
    JOIN reservations resa ON r.id = resa.room_id 
                           AND DATE(resa.start ) >=  '2016-01-10'
                           AND DATE(resa.end ) <=  '2016-01-15'
                           AND resa.status LIKE "CheckedOut"
    JOIN bills b ON resa.id = b.resa_id AND b.billed LIKE "Yes"

Notice that "rt", "r", "resa" and "b" are aliases for your tables