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]

Hi there,
You've taken a good shot at describing your problem. But forum readers might have an easier time (or at least I will!) if you wanted to post some of:

- the query you've got so far,
- the sample output this query produces,
- the actual output you really want.
- the layouts of the tables involved (if that helps us
understand)

Maybe not all this is necessary, but whatever helps us see where you are now vs where you want to be.

bp

 

[danharman]

Hi bp,

Appreciate your comments / reply; hope this helps:

Relations Involved:
DAYS(FLIGHT, DAY)
FLIGHT(FID,FFROM,FTO,DEPTTIME,ARRVTIME,DAYS,FLIGHTNO)

Note:
Days.Flight = Flight.Fid

Question:
I need to obtain the flight numbers (flight.flightno) of any flights that travel to ALL of the destinations that flightno EA031, over the duration of a week.

So far, I have managed to obtain the destinations of flightno EA031 and the days of the week it flies by:

(Select flight.flightno, flight.fto, days.day
From flight, days
Where flight.fid = days.flight
And flight.flightno = ‘EA031’)

This query gave me a result of three destinations that flight ‘EA031’ flies to. I now need to find any other flightno’s that fly to ALL of these three destinations over a week? The tried initially with an outer query that read:

Select flight.flightno, flight.fto, days.day
From flight, days
Where flight.fto in
(inner query)

Problem is, that the inner query gives me 3 different destinations, and I need any flightnos that fly to ALL 3 over a week; not just one of the destinations.

Any help would be greatly appreciated.

DJH.

 

[bperry]

Hmmm, yes I agree that the "IN (inner query)" syntax will give you flights that match any ONE of the original three cities, which is not what you want.

But I am temporarily stumped. I'll stew on it for a few hours, hopefully I (or any other forum readers) will be able to make a suggestion.

bp

 

[bperry]

Just wondering if this has to be a single SQL statement. i.e. Would a solution with a temp table (probably inside a stored procedure) be acceptable? Or is your requirement, timetable, environment, etc such that you really want a single statement?

bp

 

[danharman]

Hi bp,

Unfortunately I do need a single SQL statement.

I also tried using the exists / not exists functions, and referencing the innner query to the outside query - any with that line of thought?

Thanks for your help!

DJH

 

[bperry]

Basically query is saying this:

"Here's the set of n cities associated our flight. Do any other flights have the same set of cities?"

Perhaps saying it this way will help us see a solution.

 

[bperry]

Yikes, the person proofreading my typing is doing a lousy job.

"Here's the set of n cities associated WITH our flight. Do any other flights have the same set of cities?"

 

[bperry]

This particular example has a flight with three cities. But I suppose that number can vary, right? The next example might have 1,2,3,4,5 cities.

Also, is it okay that while our flight has 3 cities, another flight might have 4 cities, 3 of which match our original 3 cities. That would still count as a match, I imagine?

bp

 

[danharman]

hi bp,

you are correct on both accounts - the number of destinations will vary depending on which flight you query, and if a flight has 4 destinations, 3 of which match 'EA031' flight destinations, then it does counts.

i tried to look at it as follows:

* give me a list of the destinations that flight 'EA031' flies to.
* give me a list of any other flights that fly to all destinations that flight 'EA031' flies to.

just having trouble matching ALL 3 attributes....

cheers,

djh

 

[bperry]

Okay, let's try this for starters.
This returns a list of flight numbers that travel to the same cities as the original flight we are interested in. (Notice that I have to code that original flight in three spots.)

This syntax works in Microsoft SQL Server. If you're working in something else, hopefully it will be pretty close. We might have to get some help translating into your database.

Once we get this going, we'll work it up to precisely what you need, and include the DAYS table as well.
-----------------------------
Select f2.flightno, COUNT(f2.flightno) as NumCities
From FLIGHT f2
cross join
(
Select fto ,flightno
From FLIGHT
Where flightno = 'EA31'
) f1
Where f2.flightno <> 'EA31' and f2.fto = f1.fto
Group by f2.flightno
Having COUNT(f2.flightno) =
(select count(flightno) from FLIGHT
where flightno = 'EA31')
---------------------------

 

[danharman]

hi bperry,

thanks so much for your assistance; only problem is, i am using SQL not SQL2 hence can not do any JOINS. bummer.

i was thinking of getting around the problem usine exists / not exists.....here is what i have so far, however it is still not giving me the desired results!:

select flightno, fto
from flight
where not exist
(select *
from flight b
where (b.flightno in
(select fto
from flight
where flightno = 'EA056'))
and not exists
(select flightno
from flight c
where c.flightno = flightno
and c.fto = b.fto))

 

[bperry]

That's too bad, 'cause this thing works really great.

>>...SQL not SQL2 hence can not do any JOINS.
I'm not sure I'm following you here....
I am using MircoSoft SQL Server. What are you using? What is SQL2?

Perhaps you can use the older (pre-ANSI92) syntax (i.e. like Oracle uses?)

I don't believe that the exists/not exists is going to get us anywhere, at least as far as I can see.

bp

 

[danharman]

hi bp,

thanks for your assistance - i am still working on it as my deadline is approaching. you will have to excuse my ignorance re: SQL terminology - i am very new to SQL. re: the type of syntax - i am using an old version of oracle hence SQL is all i have.

not to worry, i shall perservere - thanks for all your help though!

djh

 

[bperry]

>>....i am using an old version of oracle

I don't know anything about oracle, but I feel sure it must be abel to join tables together. Although the syntax may be different than what I show.


If you do get something going, I'd be interested in seeing what your solution was.

bp

 

[danharman]

bperry,

i have managed so far to obtain all flights that fly to at least one of the destinations that flight EA31 flies to. i am thinking now, if i can count the number of destinations that flight EA31 flies to, and then apply this to an outer query, i should be almost there......any suggestions, using the method i am attempting? how can i count inner queries that have been grouped? i have attempted to do this - please see below for my sql.

thanks once again!


select flightno
from flight C
where C.fto in (select fto
from flight
where flightno = 'EA056')
group by C.flightno
having count (C.flightno) =
(select count(flightno)
from flight
where flightno = 'EA056')

 

[bperry]

I don;t think this is everything you need, but try it and see where it takes you:
------------------------
Select * from Flight where flightno IN
(
select flightno
from flight C
where C.fto in (select fto
from flight
where flightno = 'EA056')
group by C.flightno
having count (C.flightno) =
(select count(flightno)
from flight
where flightno = 'EA056')
)
---------------------------------

 

[danharman]

thanks bperry!

the result of your requested query is listed below; note that all i need to do now is get the query to give me a result of flight EA154 as this is the only flight that fliess to ALL of flight's EA056 destinations....can we count once again where count = 3?

thanks for your help!

djh

FID FFROM FTO DEPTTIME ARRVTIME DAYS FLIGHTNO
42 DLA LOP 1820 1920 0 EA056
325 NEB SVT 840 1030 0 EA056
1263 YDS NEB 735 800 0 EA056
582 IRH NEB 210 410 0 EA070
583 IRH NEB 900 1100 0 EA070
584 IRH NEB 1900 2100 0 EA070
41 DLA LOP 1520 1620 0 EA154
326 NEB SVT 1055 1245 0 EA154
1267 YDS NEB 1005 1030 0 EA154
399 NSC SVT 1750 1840 0 EA3217
400 NSC SVT 1825 1915 0 EA3217
401 NSC SVT 1900 2005 0 EA3217
343 DBB NEB 1205 1255 0 EA3235
507 LTG NEB 1045 1200 0 EA3235
508 LTG NEB 1120 1235 0 EA3235
513 LTG DBB 1120 1155 0 EA3235
163 LTB NEB 825 1000 0 EA4203
500 MDE LTB 755 815 0 EA4203
501 MDE NEB 755 930 0 EA4203
502 MDE NEB 815 1000 0 EA4203
1024 OUN NEB 1120 1245 0 EA510
1026 OUN NEB 1510 1635 0 EA510
1027 OUN NEB 1545 1710 0 EA510
1182 OKR NEB 1640 1745 0 EA689
1183 OKR NEB 1720 1825 0 EA689
1184 OKR NEB 1805 1910 0 EA689
579 IRH NEB 210 420 0 EA732
580 IRH NEB 900 1100 0 EA732
581 IRH NEB 1900 2100 0 EA732
576 KGH ELM 2130 935 1 EA88
1012 LKN NEB 710 815 0 EA88
1013 LKN NEB 1425 1530 0 EA88
1015 LKN NEB 2240 2345 0 EA88

 

[bperry]

But I don't think it works (Does it??)

For instance: EA056 flies to LOP,SVT, and NEB.

But the next flight, EA070, flies only to NEB?

Doesn't that mean the query is not returning what you want?
(I guess I don;t understand something here.)

42 DLA LOP 1820 1920 0 EA056
325 NEB SVT 840 1030 0 EA056
1263 YDS NEB 735 800 0 EA056
582 IRH NEB 210 410 0 EA070
583 IRH NEB 900 1100 0 EA070
584 IRH NEB 1900 2100 0 EA070

 

[danharman]

bperry,

my mistake, the query above is not returning the correct results i am after for the reason you mention. if we go back a step and look at the below query, we can see that the result is a list of flights that fly to at least one of the destinations that EA056 flies to (note that other destinations other thatn what EA056 flies to are admitted). on this basis, i am now trying to count the flightno of this list - as the relevant flightno that appear 3 times will have all three destinations (as duplicate destinations (ie. EA070) are removed.

hope this makes sense?

djh

select flightno, fto
from flight C
where C.fto in (select fto
from flight
where flightno = 'EA056')
group by C.flightno, C.fto

&quot;NB: Result gives all flights that fly
to at least one of the destinations that EA056 flies to.&quot;


Query Results:

FLIGHTNO
EA011
EA012
EA013
EA014
EA016
EA018
EA02
EA052
EA052
EA054
EA054
EA056
EA056
EA056
EA058
EA058
EA068
EA070
EA084
EA086
EA088
EA09
EA110
EA150
EA150
EA152
EA152
EA154
EA154
EA154
EA156
EA158
EA160
EA162
EA164
EA166
EA167
EA168
EA241
EA243
EA245
EA247
EA252
EA254
EA258
EA260
EA260
EA262
EA266
EA267
EA268
EA269
EA291
EA293
EA316
EA3200
EA3201
EA3201
EA3203
EA3204
EA3207
EA3211
EA3211
EA3214
EA3217
EA3221
EA3225
EA3231
EA3235
EA3241
EA3257
EA3263
EA3273
EA3275
EA3277
EA352
EA356
EA358
EA360
EA360
EA361
EA362
EA364
EA366
EA367
EA412
EA4203
EA4209
EA4220
EA4224
EA4226
EA4228
EA4230
EA4236
EA4238
EA4241
EA4251
EA4253
EA4255
EA4257
EA4259
EA4261
EA4263
EA4267
EA4275
EA45
EA450
EA452
EA454
EA456
EA458
EA459
EA469
EA510
EA512
EA518
EA532
EA554
EA556
EA557
EA558
EA562
EA580
EA582
EA584
EA586
EA61
EA616
EA630
EA634
EA65
EA663
EA68
EA681
EA685
EA686
EA687
EA689
EA69
EA716
EA732
EA734
EA738
EA781
EA785
EA787
EA820
EA85
EA88
EA881
EA883
EA938
EA957