in postgresql how can I join 4 tables where the first join has a constraint?
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.
1 answer

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;