Tricky Query Problem 
Author Message
 Tricky Query Problem

I have a query where I have the following two tables:

CREATE TABLE TRACTORS
(
        TRACTOR_NUM     INT             NOT NULL,
        OWNER_TXT       VARCHAR(30)     NULL
)

CREATE TABLE DRIVERS
(
        DRIVER_NUM      INT             NOT NULL,
        FNAME_TXT       VARCHAR(20)     NULL,
        LNAME_TXT       VARCHAR(20)     NULL,
        TRACTOR_NUM     INT             NULL
)

Drivers driver Tractors.  Some Tractors have two drivers these are
team drivers, some have one.  Some Drivers have no assigned Tractor.

Given the following set of data:
INSERT INTO TRACTORS VALUES (1, 'Bill Peterbuilt')
INSERT INTO TRACTORS VALUES (2, 'Steve Kenworth')
INSERT INTO TRACTORS VALUES (3, 'Al Cummings')
INSERT INTO TRACTORS VALUES (4, 'Jim White')
INSERT INTO TRACTORS VALUES (5, 'John Mack')

INSERT INTO DRIVERS VALUES (1, 'Mike', 'Andretti', 1)
INSERT INTO DRIVERS VALUES (2, 'Dale', 'Earnhardt', 2)
INSERT INTO DRIVERS VALUES (3, 'Darrell', 'Waltrip', 2)
INSERT INTO DRIVERS VALUES (4, 'Al', 'Unser', 3)
INSERT INTO DRIVERS VALUES (5, 'Rusty', 'Wallace', 4)
INSERT INTO DRIVERS VALUES (6, 'Bobby', 'Labonte', 4)
INSERT INTO DRIVERS VALUES (7, 'Jeff', 'Gordon', 5)
INSERT INTO DRIVERS VALUES (8, 'Dale', 'Jarrett', 0)
INSERT INTO DRIVERS VALUES (9, 'Sterling', 'Martin', 0)
INSERT INTO DRIVERS VALUES (10, 'Kurt', 'Busch', 0)

I would like to be able to select the set of drivers and their
tractors.  I have the following query which gives me two rows for each
of the team drivers.

select  D1.TRACTOR_NUM,
        D1.DRIVER_NUM DRIVER1_NUM,
        D1.LNAME_TXT DRIVER1_LNAME,
        D2.DRIVER_NUM DRIVER2_NUM,
        D2.LNAME_TXT DRIVER1_LNAME
from
        DRIVERS D1
left outer join
        DRIVERS D2
on
        D1.TRACTOR_NUM  =       D2.TRACTOR_NUM
and     D1.DRIVER_NUM   <>        D2.DRIVER_NUM
and     D1.TRACTOR_NUM  >    0

TRACTOR_NUM DRIVER1_NUM DRIVER1_LNAME        DRIVER2_NUM DRIVER1_LNAME
----------- ----------- -------------------- -----------
--------------------
1           1           Andretti             NULL        NULL
2           2           Earnhardt            3           Waltrip
2           3           Waltrip              2           Earnhardt
3           4           Unser                NULL        NULL
4           5           Wallace              6           Labonte
4           6           Labonte              5           Wallace
5           7           Gordon               NULL        NULL
0           8           Jarrett              NULL        NULL
0           9           Martin               NULL        NULL
0           10          Busch                NULL        NULL

(10 row(s) affected)

I would like to be able to only get one row for each tractor that has
a driver.  I must select from the driver due to volumes on the tables.

Thanks,
Thom



Sat, 12 Nov 2005 14:58:39 GMT
 Tricky Query Problem

SELECT  A.TRACTOR_NUM,A.DRIVER1_NUM,
        D1.LNAME_TXT DRIVER1_LNAME,
        A.DRIVER2_NUM,
        D2.LNAME_TXT DRIVER2_LNAME
FROM
(
SELECT TRACTOR_NUM,MIN(DRIVER_NUM) as DRIVER1_NUM,MAX
(DRIVER_NUM) as DRIVER2_NUM
FROM DRIVERS
WHERE TRACTOR_NUM > 0
GROUP BY TRACTOR_NUM) A

JOIN DRIVERS D1 ON A.TRACTOR_NUM = D1.TRACTOR_NUM AND
A.DRIVER1_NUM = D1.DRIVER_NUM
JOIN DRIVERS D2 ON A.TRACTOR_NUM = D2.TRACTOR_NUM AND
A.DRIVER2_NUM = D2.DRIVER_NUM

Hope this what you need.

George Cosoveanu

Quote:
>-----Original Message-----
>I have a query where I have the following two tables:

>CREATE TABLE TRACTORS
>(
>    TRACTOR_NUM     INT             NOT NULL,
>    OWNER_TXT       VARCHAR(30)     NULL
>)

>CREATE TABLE DRIVERS
>(
>    DRIVER_NUM      INT             NOT NULL,
>    FNAME_TXT       VARCHAR(20)     NULL,
>    LNAME_TXT       VARCHAR(20)     NULL,
>    TRACTOR_NUM     INT             NULL
>)

>Drivers driver Tractors.  Some Tractors have two drivers
these are
>team drivers, some have one.  Some Drivers have no
assigned Tractor.

>Given the following set of data:
>INSERT INTO TRACTORS VALUES (1, 'Bill Peterbuilt')
>INSERT INTO TRACTORS VALUES (2, 'Steve Kenworth')
>INSERT INTO TRACTORS VALUES (3, 'Al Cummings')
>INSERT INTO TRACTORS VALUES (4, 'Jim White')
>INSERT INTO TRACTORS VALUES (5, 'John Mack')

>INSERT INTO DRIVERS VALUES (1, 'Mike', 'Andretti', 1)
>INSERT INTO DRIVERS VALUES (2, 'Dale', 'Earnhardt', 2)
>INSERT INTO DRIVERS VALUES (3, 'Darrell', 'Waltrip', 2)
>INSERT INTO DRIVERS VALUES (4, 'Al', 'Unser', 3)
>INSERT INTO DRIVERS VALUES (5, 'Rusty', 'Wallace', 4)
>INSERT INTO DRIVERS VALUES (6, 'Bobby', 'Labonte', 4)
>INSERT INTO DRIVERS VALUES (7, 'Jeff', 'Gordon', 5)
>INSERT INTO DRIVERS VALUES (8, 'Dale', 'Jarrett', 0)
>INSERT INTO DRIVERS VALUES (9, 'Sterling', 'Martin', 0)
>INSERT INTO DRIVERS VALUES (10, 'Kurt', 'Busch', 0)

>I would like to be able to select the set of drivers and
their
>tractors.  I have the following query which gives me two
rows for each
>of the team drivers.

>select      D1.TRACTOR_NUM,
>    D1.DRIVER_NUM DRIVER1_NUM,
>    D1.LNAME_TXT DRIVER1_LNAME,
>    D2.DRIVER_NUM DRIVER2_NUM,
>    D2.LNAME_TXT DRIVER1_LNAME
>from
>    DRIVERS D1
>left outer join
>    DRIVERS D2
>on
>    D1.TRACTOR_NUM  =       D2.TRACTOR_NUM
>and         D1.DRIVER_NUM   <>        D2.DRIVER_NUM
>and         D1.TRACTOR_NUM  >    0

>TRACTOR_NUM DRIVER1_NUM DRIVER1_LNAME        DRIVER2_NUM
DRIVER1_LNAME
>----------- ----------- -------------------- -----------
>--------------------
>1           1           Andretti             NULL        
NULL
>2           2           Earnhardt            3          
Waltrip
>2           3           Waltrip              2          
Earnhardt
>3           4           Unser                NULL        
NULL
>4           5           Wallace              6          
Labonte
>4           6           Labonte              5          
Wallace
>5           7           Gordon               NULL        
NULL
>0           8           Jarrett              NULL        
NULL
>0           9           Martin               NULL        
NULL
>0           10          Busch                NULL        
NULL

>(10 row(s) affected)

>I would like to be able to only get one row for each
tractor that has
>a driver.  I must select from the driver due to volumes
on the tables.

>Thanks,
>Thom
>.



Sat, 12 Nov 2005 15:27:35 GMT
 Tricky Query Problem
Why don't you have a Tractor_Driver relation table
e.g. create table Tractor_Driver ( id int not null identity(1,1) primary
key, TRACTOR_NUM INT NOT NULL,DRIVER_NUM INT NOT NULL)

This way, a tractor can have multiple drivers and querying can be done easy.

You could also create a Team-tb. and then have a Team_Tractor relation and a
Team_Driver relation
So later on you could have a RaceHeat table with Heat_num,Tracor_num,
Driver_num ..... 3NF objects TBD

Maybe for the time being this can help out :

select D3.Tractor_Num
, DRIVER1_NUM
, DRIVER1_LNAME
, DRIVER2_NUM
, DRIVER2_LNAME
from
 (select Tractor_num, min(Driver_Num) as Driver_Num  from Drivers group by
Tractor_num) D3
left outer join
 (
 select D1.TRACTOR_NUM,
 D1.DRIVER_NUM DRIVER1_NUM,
 D1.LNAME_TXT DRIVER1_LNAME,
 D2.DRIVER_NUM DRIVER2_NUM,
 D2.LNAME_TXT DRIVER2_LNAME
 from
 DRIVERS D1
 left outer join
 DRIVERS D2
 on
 D1.TRACTOR_NUM = D2.TRACTOR_NUM
 and D1.DRIVER_NUM <> D2.DRIVER_NUM
 and D1.TRACTOR_NUM > 0
 ) D4
on D4.Tractor_num = D3.Tractor_num
and  D3.Driver_Num = D4.Driver1_Num

jobi


Quote:
> I have a query where I have the following two tables:

> CREATE TABLE TRACTORS
> (
> TRACTOR_NUM INT NOT NULL,
> OWNER_TXT VARCHAR(30) NULL
> )

> CREATE TABLE DRIVERS
> (
> DRIVER_NUM INT NOT NULL,
> FNAME_TXT VARCHAR(20) NULL,
> LNAME_TXT VARCHAR(20) NULL,
> TRACTOR_NUM INT NULL
> )

> Drivers driver Tractors.  Some Tractors have two drivers these are
> team drivers, some have one.  Some Drivers have no assigned Tractor.

> Given the following set of data:
> INSERT INTO TRACTORS VALUES (1, 'Bill Peterbuilt')
> INSERT INTO TRACTORS VALUES (2, 'Steve Kenworth')
> INSERT INTO TRACTORS VALUES (3, 'Al Cummings')
> INSERT INTO TRACTORS VALUES (4, 'Jim White')
> INSERT INTO TRACTORS VALUES (5, 'John Mack')

> INSERT INTO DRIVERS VALUES (1, 'Mike', 'Andretti', 1)
> INSERT INTO DRIVERS VALUES (2, 'Dale', 'Earnhardt', 2)
> INSERT INTO DRIVERS VALUES (3, 'Darrell', 'Waltrip', 2)
> INSERT INTO DRIVERS VALUES (4, 'Al', 'Unser', 3)
> INSERT INTO DRIVERS VALUES (5, 'Rusty', 'Wallace', 4)
> INSERT INTO DRIVERS VALUES (6, 'Bobby', 'Labonte', 4)
> INSERT INTO DRIVERS VALUES (7, 'Jeff', 'Gordon', 5)
> INSERT INTO DRIVERS VALUES (8, 'Dale', 'Jarrett', 0)
> INSERT INTO DRIVERS VALUES (9, 'Sterling', 'Martin', 0)
> INSERT INTO DRIVERS VALUES (10, 'Kurt', 'Busch', 0)

> I would like to be able to select the set of drivers and their
> tractors.  I have the following query which gives me two rows for each
> of the team drivers.

> select D1.TRACTOR_NUM,
> D1.DRIVER_NUM DRIVER1_NUM,
> D1.LNAME_TXT DRIVER1_LNAME,
> D2.DRIVER_NUM DRIVER2_NUM,
> D2.LNAME_TXT DRIVER1_LNAME
> from
> DRIVERS D1
> left outer join
> DRIVERS D2
> on
> D1.TRACTOR_NUM = D2.TRACTOR_NUM
> and D1.DRIVER_NUM <> D2.DRIVER_NUM
> and D1.TRACTOR_NUM > 0

> TRACTOR_NUM DRIVER1_NUM DRIVER1_LNAME        DRIVER2_NUM DRIVER1_LNAME
> ----------- ----------- -------------------- -----------
> --------------------
> 1           1           Andretti             NULL        NULL
> 2           2           Earnhardt            3           Waltrip
> 2           3           Waltrip              2           Earnhardt
> 3           4           Unser                NULL        NULL
> 4           5           Wallace              6           Labonte
> 4           6           Labonte              5           Wallace
> 5           7           Gordon               NULL        NULL
> 0           8           Jarrett              NULL        NULL
> 0           9           Martin               NULL        NULL
> 0           10          Busch                NULL        NULL

> (10 row(s) affected)

> I would like to be able to only get one row for each tractor that has
> a driver.  I must select from the driver due to volumes on the tables.

> Thanks,
> Thom



Sat, 12 Nov 2005 15:43:19 GMT
 Tricky Query Problem
The first query is not working fine for only one driver
(MIN(DRIVER_NUM) = MAX(DRIVER_NUM) and then you will have
Driver 1 and Driver 2 the same name)
Try using the following query instead

SELECT  A.TRACTOR_NUM,A.DRIVER1_NUM,
        D1.LNAME_TXT DRIVER1_LNAME,
        A.DRIVER2_NUM,
        D2.LNAME_TXT DRIVER2_LNAME
FROM
(
(
SELECT TRACTOR_NUM,MIN(DRIVER_NUM) as DRIVER1_NUM,MAX
(DRIVER_NUM) as DRIVER2_NUM
FROM DRIVERS
WHERE TRACTOR_NUM > 0
GROUP BY TRACTOR_NUM
HAVING MIN(DRIVER_NUM) <> MAX(DRIVER_NUM)
)
UNION ALL
(
SELECT TRACTOR_NUM,MIN(DRIVER_NUM) as DRIVER1_NUM,0 as
DRIVER2_NUM
FROM DRIVERS
WHERE TRACTOR_NUM > 0
GROUP BY TRACTOR_NUM
HAVING MIN(DRIVER_NUM) = MAX(DRIVER_NUM)
)
) A
JOIN DRIVERS D1 ON A.TRACTOR_NUM = D1.TRACTOR_NUM AND
A.DRIVER1_NUM = D1.DRIVER_NUM
JOIN DRIVERS D2 ON A.TRACTOR_NUM = D2.TRACTOR_NUM AND
A.DRIVER2_NUM = D2.DRIVER_NUM

George Cosoveanu

Quote:
>-----Original Message-----
>SELECT      A.TRACTOR_NUM,A.DRIVER1_NUM,
>        D1.LNAME_TXT DRIVER1_LNAME,
>    A.DRIVER2_NUM,
>    D2.LNAME_TXT DRIVER2_LNAME
>FROM
>(
>SELECT TRACTOR_NUM,MIN(DRIVER_NUM) as DRIVER1_NUM,MAX
>(DRIVER_NUM) as DRIVER2_NUM
>FROM DRIVERS
>WHERE TRACTOR_NUM > 0
>GROUP BY TRACTOR_NUM) A

>JOIN DRIVERS D1 ON A.TRACTOR_NUM = D1.TRACTOR_NUM AND
>A.DRIVER1_NUM = D1.DRIVER_NUM
>JOIN DRIVERS D2 ON A.TRACTOR_NUM = D2.TRACTOR_NUM AND
>A.DRIVER2_NUM = D2.DRIVER_NUM

>Hope this what you need.

>George Cosoveanu

>>-----Original Message-----
>>I have a query where I have the following two tables:

>>CREATE TABLE TRACTORS
>>(
>>        TRACTOR_NUM     INT             NOT NULL,
>>        OWNER_TXT       VARCHAR(30)     NULL
>>)

>>CREATE TABLE DRIVERS
>>(
>>        DRIVER_NUM      INT             NOT NULL,
>>        FNAME_TXT       VARCHAR(20)     NULL,
>>        LNAME_TXT       VARCHAR(20)     NULL,
>>        TRACTOR_NUM     INT             NULL
>>)

>>Drivers driver Tractors.  Some Tractors have two drivers
>these are
>>team drivers, some have one.  Some Drivers have no
>assigned Tractor.

>>Given the following set of data:
>>INSERT INTO TRACTORS VALUES (1, 'Bill Peterbuilt')
>>INSERT INTO TRACTORS VALUES (2, 'Steve Kenworth')
>>INSERT INTO TRACTORS VALUES (3, 'Al Cummings')
>>INSERT INTO TRACTORS VALUES (4, 'Jim White')
>>INSERT INTO TRACTORS VALUES (5, 'John Mack')

>>INSERT INTO DRIVERS VALUES (1, 'Mike', 'Andretti', 1)
>>INSERT INTO DRIVERS VALUES (2, 'Dale', 'Earnhardt', 2)
>>INSERT INTO DRIVERS VALUES (3, 'Darrell', 'Waltrip', 2)
>>INSERT INTO DRIVERS VALUES (4, 'Al', 'Unser', 3)
>>INSERT INTO DRIVERS VALUES (5, 'Rusty', 'Wallace', 4)
>>INSERT INTO DRIVERS VALUES (6, 'Bobby', 'Labonte', 4)
>>INSERT INTO DRIVERS VALUES (7, 'Jeff', 'Gordon', 5)
>>INSERT INTO DRIVERS VALUES (8, 'Dale', 'Jarrett', 0)
>>INSERT INTO DRIVERS VALUES (9, 'Sterling', 'Martin', 0)
>>INSERT INTO DRIVERS VALUES (10, 'Kurt', 'Busch', 0)

>>I would like to be able to select the set of drivers and
>their
>>tractors.  I have the following query which gives me two
>rows for each
>>of the team drivers.

>>select  D1.TRACTOR_NUM,
>>        D1.DRIVER_NUM DRIVER1_NUM,
>>        D1.LNAME_TXT DRIVER1_LNAME,
>>        D2.DRIVER_NUM DRIVER2_NUM,
>>        D2.LNAME_TXT DRIVER1_LNAME
>>from
>>        DRIVERS D1
>>left outer join
>>        DRIVERS D2
>>on
>>        D1.TRACTOR_NUM  =       D2.TRACTOR_NUM
>>and     D1.DRIVER_NUM   <>        D2.DRIVER_NUM
>>and     D1.TRACTOR_NUM  >    0

>>TRACTOR_NUM DRIVER1_NUM DRIVER1_LNAME        DRIVER2_NUM
>DRIVER1_LNAME
>>----------- ----------- -------------------- -----------
>>--------------------
>>1           1           Andretti             NULL        
>NULL
>>2           2           Earnhardt            3          
>Waltrip
>>2           3           Waltrip              2          
>Earnhardt
>>3           4           Unser                NULL        
>NULL
>>4           5           Wallace              6          
>Labonte
>>4           6           Labonte              5          
>Wallace
>>5           7           Gordon               NULL        
>NULL
>>0           8           Jarrett              NULL        
>NULL
>>0           9           Martin               NULL        
>NULL
>>0           10          Busch                NULL        
>NULL

>>(10 row(s) affected)

>>I would like to be able to only get one row for each
>tractor that has
>>a driver.  I must select from the driver due to volumes
>on the tables.

>>Thanks,
>>Thom
>>.

>.



Sat, 12 Nov 2005 16:01:50 GMT
 Tricky Query Problem
Simple solution with a derived table, not necessarily the fastest or
neatest:

SELECT * FROM(
select D1.TRACTOR_NUM,
D1.DRIVER_NUM DRIVER1_NUM,
D1.LNAME_TXT DRIVER1_LNAME,
D2.DRIVER_NUM DRIVER2_NUM,
D2.LNAME_TXT DRIVER2_LNAME
from
DRIVERS D1
left outer join
DRIVERS D2
on
D1.TRACTOR_NUM = D2.TRACTOR_NUM
and D1.DRIVER_NUM <> D2.DRIVER_NUM
and D1.TRACTOR_NUM > 0
) As x
WHERE DRIVER1_NUM < DRIVER2_NUM OR DRIVER2_NUM IS NULL

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:
> I have a query where I have the following two tables:

> CREATE TABLE TRACTORS
> (
> TRACTOR_NUM INT NOT NULL,
> OWNER_TXT VARCHAR(30) NULL
> )

> CREATE TABLE DRIVERS
> (
> DRIVER_NUM INT NOT NULL,
> FNAME_TXT VARCHAR(20) NULL,
> LNAME_TXT VARCHAR(20) NULL,
> TRACTOR_NUM INT NULL
> )

> Drivers driver Tractors.  Some Tractors have two drivers these are
> team drivers, some have one.  Some Drivers have no assigned Tractor.

> Given the following set of data:
> INSERT INTO TRACTORS VALUES (1, 'Bill Peterbuilt')
> INSERT INTO TRACTORS VALUES (2, 'Steve Kenworth')
> INSERT INTO TRACTORS VALUES (3, 'Al Cummings')
> INSERT INTO TRACTORS VALUES (4, 'Jim White')
> INSERT INTO TRACTORS VALUES (5, 'John Mack')

> INSERT INTO DRIVERS VALUES (1, 'Mike', 'Andretti', 1)
> INSERT INTO DRIVERS VALUES (2, 'Dale', 'Earnhardt', 2)
> INSERT INTO DRIVERS VALUES (3, 'Darrell', 'Waltrip', 2)
> INSERT INTO DRIVERS VALUES (4, 'Al', 'Unser', 3)
> INSERT INTO DRIVERS VALUES (5, 'Rusty', 'Wallace', 4)
> INSERT INTO DRIVERS VALUES (6, 'Bobby', 'Labonte', 4)
> INSERT INTO DRIVERS VALUES (7, 'Jeff', 'Gordon', 5)
> INSERT INTO DRIVERS VALUES (8, 'Dale', 'Jarrett', 0)
> INSERT INTO DRIVERS VALUES (9, 'Sterling', 'Martin', 0)
> INSERT INTO DRIVERS VALUES (10, 'Kurt', 'Busch', 0)

> I would like to be able to select the set of drivers and their
> tractors.  I have the following query which gives me two rows for each
> of the team drivers.

> select D1.TRACTOR_NUM,
> D1.DRIVER_NUM DRIVER1_NUM,
> D1.LNAME_TXT DRIVER1_LNAME,
> D2.DRIVER_NUM DRIVER2_NUM,
> D2.LNAME_TXT DRIVER1_LNAME
> from
> DRIVERS D1
> left outer join
> DRIVERS D2
> on
> D1.TRACTOR_NUM = D2.TRACTOR_NUM
> and D1.DRIVER_NUM <> D2.DRIVER_NUM
> and D1.TRACTOR_NUM > 0

> TRACTOR_NUM DRIVER1_NUM DRIVER1_LNAME        DRIVER2_NUM DRIVER1_LNAME
> ----------- ----------- -------------------- -----------
> --------------------
> 1           1           Andretti             NULL        NULL
> 2           2           Earnhardt            3           Waltrip
> 2           3           Waltrip              2           Earnhardt
> 3           4           Unser                NULL        NULL
> 4           5           Wallace              6           Labonte
> 4           6           Labonte              5           Wallace
> 5           7           Gordon               NULL        NULL
> 0           8           Jarrett              NULL        NULL
> 0           9           Martin               NULL        NULL
> 0           10          Busch                NULL        NULL

> (10 row(s) affected)

> I would like to be able to only get one row for each tractor that has
> a driver.  I must select from the driver due to volumes on the tables.

> Thanks,
> Thom



Sat, 12 Nov 2005 16:19:19 GMT
 Tricky Query Problem
Unfortunately, this is a reporting database.  I cannot create or modify
any of the tables.

Thanks anyway,
Thom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 12 Nov 2005 16:20:12 GMT
 Tricky Query Problem

Quote:

> I have a query where I have the following two tables:

> CREATE TABLE TRACTORS
> (
> TRACTOR_NUM INT NOT NULL,
> OWNER_TXT VARCHAR(30) NULL
> )

> CREATE TABLE DRIVERS
> (
> DRIVER_NUM INT NOT NULL,
> FNAME_TXT VARCHAR(20) NULL,
> LNAME_TXT VARCHAR(20) NULL,
> TRACTOR_NUM INT NULL
> )

> Drivers driver Tractors.  Some Tractors have two drivers these are
> team drivers, some have one.  Some Drivers have no assigned Tractor.

> Given the following set of data:
> INSERT INTO TRACTORS VALUES (1, 'Bill Peterbuilt')
> INSERT INTO TRACTORS VALUES (2, 'Steve Kenworth')
> INSERT INTO TRACTORS VALUES (3, 'Al Cummings')
> INSERT INTO TRACTORS VALUES (4, 'Jim White')
> INSERT INTO TRACTORS VALUES (5, 'John Mack')

> INSERT INTO DRIVERS VALUES (1, 'Mike', 'Andretti', 1)
> INSERT INTO DRIVERS VALUES (2, 'Dale', 'Earnhardt', 2)
> INSERT INTO DRIVERS VALUES (3, 'Darrell', 'Waltrip', 2)
> INSERT INTO DRIVERS VALUES (4, 'Al', 'Unser', 3)
> INSERT INTO DRIVERS VALUES (5, 'Rusty', 'Wallace', 4)
> INSERT INTO DRIVERS VALUES (6, 'Bobby', 'Labonte', 4)
> INSERT INTO DRIVERS VALUES (7, 'Jeff', 'Gordon', 5)
> INSERT INTO DRIVERS VALUES (8, 'Dale', 'Jarrett', 0)
> INSERT INTO DRIVERS VALUES (9, 'Sterling', 'Martin', 0)
> INSERT INTO DRIVERS VALUES (10, 'Kurt', 'Busch', 0)

> I would like to be able to select the set of drivers and their
> tractors.  I have the following query which gives me two rows for each
> of the team drivers.

> select D1.TRACTOR_NUM,
> D1.DRIVER_NUM DRIVER1_NUM,
> D1.LNAME_TXT DRIVER1_LNAME,
> D2.DRIVER_NUM DRIVER2_NUM,
> D2.LNAME_TXT DRIVER1_LNAME
> from
> DRIVERS D1
> left outer join
> DRIVERS D2
> on
> D1.TRACTOR_NUM = D2.TRACTOR_NUM
> and D1.DRIVER_NUM <> D2.DRIVER_NUM
> and D1.TRACTOR_NUM > 0

> TRACTOR_NUM DRIVER1_NUM DRIVER1_LNAME        DRIVER2_NUM DRIVER1_LNAME
> ----------- ----------- -------------------- -----------
> --------------------
> 1           1           Andretti             NULL        NULL
> 2           2           Earnhardt            3           Waltrip
> 2           3           Waltrip              2           Earnhardt
> 3           4           Unser                NULL        NULL
> 4           5           Wallace              6           Labonte
> 4           6           Labonte              5           Wallace
> 5           7           Gordon               NULL        NULL
> 0           8           Jarrett              NULL        NULL
> 0           9           Martin               NULL        NULL
> 0           10          Busch                NULL        NULL

> (10 row(s) affected)

> I would like to be able to only get one row for each tractor that has
> a driver.  I must select from the driver due to volumes on the tables.

> Thanks,
> Thom

It's not pretty but...

SELECT T.tractor_num,
               MIN(D.driver_num) AS driver1_num,
               (SELECT lname_txt
                FROM Drivers
                WHERE driver_num = MIN(D.driver_num)) AS driver1_lname,
               NULLIF(MAX(D.driver_num), MIN(D.driver_num)) AS driver2_num,
               CASE WHEN MAX(D.driver_num) = MIN(D.driver_num)
                          THEN NULL
                          ELSE
                          (SELECT lname_txt
                           FROM Drivers
                           WHERE driver_num = MAX(D.driver_num))
               END AS driver2_lname
FROM Tractors AS T
            LEFT OUTER JOIN
            Drivers AS D
            ON T.tractor_num = D.tractor_num
GROUP BY T.tractor_num
UNION ALL
SELECT D.tractor_num,
               D.driver_num AS driver1_num,
               D.lname_txt AS driver1_lname,
               NULL AS driver2_num,
               NULL AS driver2_lname
FROM Drivers AS D
WHERE NOT EXISTS (SELECT *
                                       FROM Tractors
                                       WHERE D.tractor_num = tractor_num)
ORDER BY T.tractor_num

tractor_num driver1_num driver1_lname driver2_num driver2_lname
0 8 Jarrett NULL NULL
0 9 Martin NULL NULL
0 10 Busch NULL NULL
1 1 Andretti NULL NULL
2 2 Earnhardt 3 Waltrip
3 4 Unser NULL NULL
4 5 Wallace 6 Labonte
5 7 Gordon NULL NULL

Regards,
jag



Sat, 12 Nov 2005 16:23:01 GMT
 Tricky Query Problem
Thom,

  I am not entirely sure if this is what you want, but give it a try:

SELECT
  D.TRACTOR_NUM,
    D.MINDRIVER DRIVER1_NUM,
    N1.LNAME_TXT DRIVER1_LNAME,
    D.NEXTDRIVER DRIVER2_NUM,
    N2.LNAME_TXT DRIVER1_LNAME
FROM (
  SELECT
    D1.TRACTOR_NUM,
    MIN(D1.DRIVER_NUM) AS MINDRIVER,
    MIN(D2.DRIVER_NUM) AS NEXTDRIVER
  FROM DRIVERS D1
  LEFT JOIN DRIVERS D2
  ON D1.DRIVER_NUM < D2.DRIVER_NUM
  AND D1.TRACTOR_NUM = D2.TRACTOR_NUM
  GROUP BY D1.TRACTOR_NUM
  HAVING COUNT(D2.DRIVER_NUM) <= 1
  -- excludes tractors (like tractor 0) with more than two drivers
 ) D
JOIN DRIVERS N1
ON D.MINDRIVER = N1.DRIVER_NUM
JOIN DRIVERS N2
ON D.MINDRIVER = N2.DRIVER_NUM

Steve Kass
Drew University

Quote:

>I have a query where I have the following two tables:

>CREATE TABLE TRACTORS
>(
>    TRACTOR_NUM     INT             NOT NULL,
>    OWNER_TXT       VARCHAR(30)     NULL
>)

>CREATE TABLE DRIVERS
>(
>    DRIVER_NUM      INT             NOT NULL,
>    FNAME_TXT       VARCHAR(20)     NULL,
>    LNAME_TXT       VARCHAR(20)     NULL,
>    TRACTOR_NUM     INT             NULL
>)

>Drivers driver Tractors.  Some Tractors have two drivers these are
>team drivers, some have one.  Some Drivers have no assigned Tractor.

>Given the following set of data:
>INSERT INTO TRACTORS VALUES (1, 'Bill Peterbuilt')
>INSERT INTO TRACTORS VALUES (2, 'Steve Kenworth')
>INSERT INTO TRACTORS VALUES (3, 'Al Cummings')
>INSERT INTO TRACTORS VALUES (4, 'Jim White')
>INSERT INTO TRACTORS VALUES (5, 'John Mack')

>INSERT INTO DRIVERS VALUES (1, 'Mike', 'Andretti', 1)
>INSERT INTO DRIVERS VALUES (2, 'Dale', 'Earnhardt', 2)
>INSERT INTO DRIVERS VALUES (3, 'Darrell', 'Waltrip', 2)
>INSERT INTO DRIVERS VALUES (4, 'Al', 'Unser', 3)
>INSERT INTO DRIVERS VALUES (5, 'Rusty', 'Wallace', 4)
>INSERT INTO DRIVERS VALUES (6, 'Bobby', 'Labonte', 4)
>INSERT INTO DRIVERS VALUES (7, 'Jeff', 'Gordon', 5)
>INSERT INTO DRIVERS VALUES (8, 'Dale', 'Jarrett', 0)
>INSERT INTO DRIVERS VALUES (9, 'Sterling', 'Martin', 0)
>INSERT INTO DRIVERS VALUES (10, 'Kurt', 'Busch', 0)

>I would like to be able to select the set of drivers and their
>tractors.  I have the following query which gives me two rows for each
>of the team drivers.

>select      D1.TRACTOR_NUM,
>    D1.DRIVER_NUM DRIVER1_NUM,
>    D1.LNAME_TXT DRIVER1_LNAME,
>    D2.DRIVER_NUM DRIVER2_NUM,
>    D2.LNAME_TXT DRIVER1_LNAME
>from
>    DRIVERS D1
>left outer join
>    DRIVERS D2
>on
>    D1.TRACTOR_NUM  =       D2.TRACTOR_NUM
>and         D1.DRIVER_NUM   <>        D2.DRIVER_NUM
>and         D1.TRACTOR_NUM  >    0

>TRACTOR_NUM DRIVER1_NUM DRIVER1_LNAME        DRIVER2_NUM DRIVER1_LNAME
>----------- ----------- -------------------- -----------
>--------------------
>1           1           Andretti             NULL        NULL
>2           2           Earnhardt            3           Waltrip
>2           3           Waltrip              2           Earnhardt
>3           4           Unser                NULL        NULL
>4           5           Wallace              6           Labonte
>4           6           Labonte              5           Wallace
>5           7           Gordon               NULL        NULL
>0           8           Jarrett              NULL        NULL
>0           9           Martin               NULL        NULL
>0           10          Busch                NULL        NULL

>(10 row(s) affected)

>I would like to be able to only get one row for each tractor that has
>a driver.  I must select from the driver due to volumes on the tables.

>Thanks,
>Thom



Sat, 12 Nov 2005 16:27:40 GMT
 Tricky Query Problem
George,

Thanks, but that didn't quite work.  When I run the query, I only get
two rows.  

Earnhardt and Waltrip
Wallace and Labonte

The rows with only one driver do not show up.

Thanks for trying,
Thom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 12 Nov 2005 16:31:25 GMT
 Tricky Query Problem
Jacco,

Thanks, that looks like it will do the trick!

I will try it against our production data and let you know.  Thanks
again,
Thom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 12 Nov 2005 16:31:25 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Tricky UPDATE / Select SQL query problem.

2. Tricky query for me, should be simple for you

3. tricky CASE WHEN query...

4. A Tuff Tricky One - Please Help with Queries

5. tricky query (for me atleast)

6. Tricky "most recent sales" query

7. Tricky query question.

8. Tricky update query

9. tricky query

10. tricky query?

11. tricky query - please help

12. Tricky query


 
Powered by phpBB® Forum Software