Query Question

I have a question that I will illustrate with the script below. As you can
see there two tables, airlines and routes. Each airline has a customer_rank
that indicates the preferred airline of the customer ie 1 is most preferred.
Each route has a name, cost and duration.
What I would like to do is write a query that returns one row for each route
with the columns : airline, cost and duration. I would like the airline
column to be based on the preferred carrier, irrespective of the cost and
duration.
Can someone please stop my head spinning?
Thanks, Tad
CREATE TABLE [dbo].[Airlines] (
[Airline] [char] (10) PRIMARY KEY,
[Customer_Rank] [tinyint] NOT NULL
)
CREATE TABLE [dbo].[Routes] (
[ID] [int] IDENTITY (1, 1) PRIMARY KEY,
[Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
[Route] [char] (10) NOT NULL ,
[Cost] [numeric](18, 0) NOT NULL ,
[Duration] [numeric](18, 0) NOT NULL
)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('BOAC',3)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('TWA',2)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('United',1)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'A2B', 150, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('TWA', 'A2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'B2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('United', 'A2B', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('United', 'D2E', 300, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Du
ration])
VALUES('BOAC', 'B2D', 300, 3)first of all you want to know which airline is the preferred carrier
for each route, so you have to inner join the two tables and find the
airline with the lowest (if highest substitute min by max) rank:
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
with this query you get the the ranking of the preferred airline per
route. This output has to be inner joined with the airlines and routes
to get the desired result
select *
from airlines a,
routes r,
(
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
) p
where a.customer_rank = p.rank
and r.route = p.route
and a.airline = r.airline
if you want to avoid an inner table (the stuff between (...)p) you
could create a view, but you will learn that in your next class.|||Hi
Thanks for the DDL and Example data, showing your expected results would
also be nice. You can either use a subquery in the where clause or a derived
table such as
SELECT A.[Customer_Rank],
R.[ID], R.[Airline], R.[Route], R.[Cost], R.[Duration]
FROM [dbo].[Routes] R
JOIN [dbo].[Airlines] A ON A.[Airline] = R.[Airline]
JOIN ( SELECT MIN(E.[Customer_Rank]) AS [Customer_Rank], D.[Rout
e]
FROM [dbo].[Routes] D
JOIN [dbo].[Airlines] E ON D.[Airline] = E.[Airline]
GROUP BY D.[Route] ) F ON F.[Customer_Rank] = A.[Customer_Rank]
AND
F.[Route] = R.[Route]
ORDER BY R.[Route], A.[Customer_Rank]
John
"Tadwick" wrote:

> I have a question that I will illustrate with the script below. As you ca
n
> see there two tables, airlines and routes. Each airline has a customer_ra
nk
> that indicates the preferred airline of the customer ie 1 is most preferre
d.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each rou
te
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2D', 300, 3)|||Nite4Hawks and John,
You are both awesome. I am amazed to get responses with two different
techniques in such a short time. Are there pros and cons of the correlated
subquery vs derived table methods?
Thanks again, Tad
"Tadwick" wrote:

> I have a question that I will illustrate with the script below. As you ca
n
> see there two tables, airlines and routes. Each airline has a customer_ra
nk
> that indicates the preferred airline of the customer ie 1 is most preferre
d.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each rou
te
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [
Duration])
> VALUES('BOAC', 'B2D', 300, 3)