Re: How To Do 3-Way Join??

From: Rob Medley <medleyrk_at_iafrica.com>
Date: 1997/10/25
Message-ID: <34512801.F2@iafrica.com>#1/1

>
> I'm using Oracle 7.3.3 and need to join 3 tables and pull data out of each
> of them. The DRIVER and TRAILER tables could have multiple records for one
> route.
>
> ROUT: ROUT_NBR, ROUT_DESC
> DRIVER: ROUT_NBR, DRIVER_NBR
> TRAILER: ROUT_NBR, TRAILER_NBR
>
> I'm looking for a result like this where a route could have two different
> drivers or two different trailers.
>
> ROUT_NBR ROUT_DESC DRIVER_NBR TRAILER_NBR
> 1 Route 1 2
> 4
> 1 Route 1 2
> 5
> 2 Route 2 3
> 7
> 2 Route 2 4
> 7
>
> Any assistance you can provide on SQL Syntax would be helpful. I have
> checked a number of manuals, but they have had little in the way of example
> code.

By the description of your tables I am assuming the following :

1. There is a one to many relationship between rout and driver.
2. There is a one to many relationship between rout and trailer.
3. There is no relationship between driver and trailer.

-------------         --------------
|   ROUT    |         |  DRIVER    |
 -------------         --------------
| rout_nbr  |--------<| rout-nbr   |
| rout-desc |         | driver_nbr |
-------------         --------------
      | 
      |               --------------
      |               |  TRAILER   |
      \               -------------- 
       --------------<| rout_nbr   |
                      | trailer_nbr|
                      --------------              

I can think of two SQL statements that will probably give you what you want. I'm sure there are some SQL experts out there somewhere who can make it even better.

Solution 1 :

select
 rout.rout_nbr,
 rout.rout_desc,
 driver.driver_nbr,
 trailer.trailer_nbr
from
 rout,
 driver,
 trailer
where
 driver.rout_nbr = rout.rout_nbr
 and
 trailer.rout_nbr = rout.rout_nbr
/

Note : This sql will give you the driver and trailer for each rout providing that each rout has at least one driver and one trailer. If a rout has many drivers but no trailers, this sql will not show you the drivers and vice-versa.

The second Solution does not have this problem, and since there is no relationship between driver and trailer is (in my opinion) more of an accurate representation of the data

Solution 2 :

select
 rout.rout_nbr,
 rout.rout_desc,
 driver.driver_nbr,
 0 as trailer_nbr
from
 rout,
 driver
where
 driver.rout_nbr = rout.rout_nbr
union
select
 rout.rout_nbr,
 rout.rout_desc,
 0 as driver_nbr,
 trailer.trailer_nbr
from
 rout,
 trailer
where
 trailer.rout_nbr = rout.rout_nbr
/

Good Luck

Rob Received on Sat Oct 25 1997 - 00:00:00 CDT