SQL - Queries on sub-queries. 1

[danharman]

Hi,

Hopefully someone can assist.........I need to query a sub-query, based on the outer query criteria matching ALL of the attributes obtained from the inner query. For example, I have two relations A and B. My inner query creates a new relation with 3 attributes from relation A. I then want the outer query to give me certain attributes from relation B when they match ALL of the 3 attributes found in the inner query? Can I use exists to match all criteria?

Any help would be greatly appreciated.

Dan.

 

[bperry]

I think I'm too confused tonight.

(1) The query you sent me earlier today (and I modified for you) works great against my own test data. I thought you had written a great query. So now I can't tell why it works for me but not you. I wonder if my table can be that different than yours.

(2) i can't understand why in the summarized data, EA070 has three records, but in the lonnnng query result, there is only one.
582 IRH NEB 210 410 0 EA070
583 IRH NEB 900 1100 0 EA070
584 IRH NEB 1900 2100 0 EA070

I dunno where to go next. Perhaps the powers-that-be will get a database product that will allow you to do joins.

That's it for tonight. Perhaps after 24 hours some new idea or approach will develop.

bp

 

[danharman]

bp,

much appreciated the time you have spent on this query of mine - i shall too endeavour to find a solution. if so, i shall post it immediately to the forum!

cheers,

djh.

 

[danharman]

bp,

finally solved the query! as you will see below, we were so close!

thanks for your assistance

djh

select flightno
from flight
where flightno <> 'EA056'
and flightno not in
(select flightno
from flight C
where C.fto not in (select fto
from flight
where flightno = 'EA056'))
group by flightno
having count (distinct fto) =
(select count (fto)
from flight
where flightno = 'EA056')

Query Results:
FLIGHTNO
EA154