Re: SQL Unmatched records Question

"Charcoal" <who_at_cares.com> wrote in message <news:_mcL6.264$V5.1159_at_news1.rivrw1.nsw.optushome.com.au>...

> Hi again,
>
> I have two tables in an Oracle database and I want to display all the
> records in one table which are NOT in the other table. Let me explain using
> the schema below:
>
> Seats(SeatID) which ranges from 1 to 10. {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
> SoldSeats(FlightID, SeatID) which defines which seats have been sold for a
> particular flight.
>
> So for example, if seats 1,2,3 have been sold for FlightID 100 then I would
> want it to return
>
> 100 4
> 100 5
> 100 6
> 100 7
> 100 8
> 100 9
> 100 10
>
> But if a seat has been sold in another Flight, then it shouldnt affect
> Flight 100's results. So what is the SQL Query to do something like this?

I don't do Oracle, and since I'm seeing this post in comp.databases.theory, I'll go ahead and use SQL pseudocode, and the Oracle bigots can go hang. See which of these approaches works best for you:

parameters whichflight integer;
select whichflight, seatid
from seats
where not exists (select * from soldseats  where soldseats.flightid = whichflight and soldseats.seatid = seats.seatid)

You could also do something like this. IIf(x,y,z) is like C's conditional x?y:z operator, assuming that if x is Null, the result is z.

parameters whichflight integer;
select seats.seatid
from seats left join soldseats on seats.seatid = soldseats.seatid group by seats.seatid
having sum(iif(soldseats.flightid = whichflight, 1, 0)) = 0

Max() would work just as well. Or maybe this:

parameters whichflight integer;
select whichflight, seatid
from seats
MINUS
select flightid, seatid
from soldseats
where soldseats.flightid = whichflight

--
Joe Foster <mailto:jfoster_at_ricochet.net>  Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!