Re: SQL syntax hel!

In article <ffo66.12471$0s3.618050_at_typhoon.columbus.rr.com>,   "Mike" <mike_at_nospam.com> wrote:
> I wonder if someone could help me to resolve the following problem.
> I need to join 3 table:
> OUTAGE: unit, line, xdate, status, outage_id
> DISPATCH: d_code, d_outage_id, d_truck_id, d_date
> TRUCK: t_code, t_truck_id, t_truck_name
>
> select status, t_ruck_name|| '::' ||d_truck_id|| '::'
||d_date ''INFO" from
> OUTAGE,DISPATCH,TRUCK
> where xdate>sysdate-1 and (
> (status='0' and outage_id is null) or
> (status='1' and d_code='AAA' and d_code=t_code and
 d_outage_id=t_outage_id
> and d_truck_id=t_truck_id)
> )
>
> The problem is when STATUS='0' ==> OUTAGE_IS the column INFO should
 not
> have any values.
> (it cannot be connected to DISPATH.d_outage_id ( d_outage_id is never
 NULL)
> But because I am dooing something worng (that is I am sure of :-)
 with a 3
> table join, INFO always returned with some value.
>
> Basicly, result should be:
> STATUS INFO
> --------- -----------------------------
> 0
> 0
> 1 JonhDoe::truck_1::12-jan-00
> 1 RickTop::truck_2::14-jan-00
>
> (But the above query returns:
> STATUS INFO
> --------- -----------------------------
> 0 JonhDoe::truck_1::12-jan-00
> 0 JonhDoe::truck_1::12-jan-00
> 0 RickTop::truck_2::14-jan-00
> 0 RickTop::truck_2::14-jan-00
> 1 JonhDoe::truck_1::12-jan-00
> 1 RickTop::truck_2::14-jan-00
>
> I would appreciate your help.
> Thanks.
> Mike
>
>

You are generating a cartenian join basically joining all the tables together. You must always explicitly have at least one condition in the where clause joining it to at least one other table. ( Basic SQL ).

To have a table in the join sometimes, you create an outer join.

For example:

OUTAGE: unit, line, xdate, status, outage_id DISPATCH: d_code, d_outage_id, d_truck_id, d_date TRUCK: t_code, t_truck_id, t_truck_name

select
 ot.status,
 tr.t_ruck_name|| '::' ||dis.d_truck_id|| '::' || dis.d_date ''INFO" from

        outage ot,
        dispatch dis,
        truck tr
where
        ot.outage_id = dis.d_outage_id(+)
        and dis.truck_id = t.truck_id(+)

should be the start.

Note that the test for ot.outage_id being null is not needed. By definition, if the dt.outage_id is null, then none of the data from the other two tables is included. This also would allow an outage without a truck, if that made sense.

If the code is needed, (there are other codes that you want to ignore), add :

        and dis.d_code(+) = 'AAA'

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/