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

  • answered 2018-10-11 19:58 Mureinik

    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;