Difficulty using Outer Join

Question

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.

I removed the Oracle tag because the SQL fiddle uses Postgres. — Gordon Linoff
– Gordon Linoff, Commented Oct 8, 2017 at 13:08
@GordonLinoff Can you please look at this problem. I am really stuck in this. — Sujan Shrestha
– Sujan Shrestha, Commented Oct 8, 2017 at 18:57
What doesn't work about your query? Error? Undesired results? And is it really Jan 1 of next year, 2018? Also, what non-outer query works? Please post it. — Parfait
– Parfait, Commented Oct 8, 2017 at 21:10
b.day = to_date('2017-10-01','YYYY-MM-DD') needs to go into the join condition, not the where clause.The way you use it, it effectively turns the outer join into an inner join — user330315
– user330315, Commented Oct 9, 2017 at 10:44

Gurwinder Singh · Accepted Answer · 2017-10-09 10:38:36Z

1

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;

edited Oct 9, 2017 at 10:38

answered Oct 8, 2017 at 9:16

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sujan Shrestha Over a year ago

Since its a left outer join this query returns records of flight A1. Flight A1 has no reservation made on 2017-10-01.

Gurwinder Singh Over a year ago

@SujanShrestha - Updated the answer. Please try now

Collectives™ on Stack Overflow

Difficulty using Outer Join

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related