CODEWITHSUNDEEP
sqlpostgresql
Sujan Shrestha
Sujan Shrestha

Reputation: 1040

Difficulty using Outer Join

I am having problem writing query for this question using outer join. I can write this without outer join but its giving me some hard time with outer join.

Question:

For each flight on ’2018-01-01’, find all of the seats that are not yet reserved (use an outer join). For each unreserved seat, show the flight number and the seat number.

I have following tables:

create table passenger ( 
    passenger_ID varchar ( 10 ) , 
    passenger_name varchar ( 30 ) , 
    passenger_city varchar ( 30 ) , 
    primary key ( passenger_ID ) ) ;

create table flight ( 
    flight_number varchar ( 10 ) , 
    departure_airport varchar ( 10), 
    arrival_airport varchar ( 10 ) , 
    primary key ( flight_number ) );

create table seat ( 
flight_number varchar ( 10 ),
seat_number varchar ( 10 ),
primary key(flight_number, seat_number),
foreign key(flight_number) references flight);

create table reservation(
    passenger_ID varchar (10),
                         flight_number varchar (10), 
                         seat_number varchar ( 10 ), 
                         day date ,
                         fare numeric(8,2),
primary key (flight_number, seat_number,day),
foreign key (flight_number, seat_number) references seat  ,
foreign key (passenger_ID) references passenger );

Please look at this sql fiddle. I have built the schema. [http://sqlfiddle.com/#!17/feaca/1

The query i have now is:

Select distinct a.flight_number , a.seat_number
from 
seat a
left outer join 
reservation b
on 
a.flight_number = b.flight_number
and 
a.seat_number <> b.seat_number
where b.day = to_date('2017-10-01','YYYY-MM-DD') 
order by 1;

Any suggestion and help will be highly appreciated.

Upvotes: 1

Views: 65

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You want to left join on flight_number, seat_number as well as the given date. Then you can filter out the rows where there is no corresponding row in the reservations.

Select distinct s.flight_number , s.seat_number
from seat s
left outer join reservation r
    on r.flight_number = s.flight_number
    and r.seat_number = s.seat_number
    and r.day = to_date('2017-10-01','YYYY-MM-DD')
join (
    select distinct flight_number
    from reservation
    where day = to_date('2017-10-01','YYYY-MM-DD')
) r2 on s.flight_number = r2.flight_number
where r.day is null
order by 1;

Or Using IN:

Select distinct s.flight_number , s.seat_number
from seat s
left outer join reservation r
    on r.flight_number = s.flight_number
    and r.seat_number = s.seat_number
    and r.day = to_date('2017-10-01','YYYY-MM-DD')
where r.day is null
and s.flight_number in (
    select flight_number
    from reservation
    where day = to_date('2017-10-01','YYYY-MM-DD')
    )
order by 1;

Upvotes: 1

Related Questions