in postgresql how can I join 4 tables where the first join has a constraint?

Question

I want to join 4 tables but with the first one having a constraint. This is the join without the constraint that works fine:

    SELECT room_number, rent_rate, leases.place_number, students.id, 
           students.first_name, students.last_name
      FROM rooms
INNER JOIN hall_rooms ON rooms.place_number = hall_rooms.place_number 
INNER JOIN leases ON leases.place_number = rooms.place_number
INNER JOIN students ON students.id = leases.place_number;

But I want to put the constraint "where hall_rooms.hall_id = 1" in the first join clause. is it possible?

I have tried to put it directly after the first join class / before the second inner join but it does not work.

edit: I don't want to include the column hall_rooms.hall_id in the resulting table, i just want the constraint to be true.

Answer 1

You could add it to the join condition with the and logical operator:

    SELECT room_number, rent_rate, leases.place_number, students.id, 
            students.first_name, students.last_name
      FROM rooms
INNER JOIN hall_rooms ON rooms.place_number = hall_rooms.place_number 
INNER JOIN leases ON (
                       leases.place_number = rooms.place_number AND 
                       hall_rooms.hall_id = 1 -- here!
                     ) 
INNER JOIN students ON students.id = leases.place_number;