Re: How to get a distinct count of result set of multople table joins?

From: Peter Nilsson <airia_at_acay.com.au>
Date: Thu, 28 Feb 2008 14:16:59 -0800 (PST)
Message-ID: <f9ee86ed-acba-4fff-9443-7e8f85b08df8@n58g2000hsf.googlegroups.com>

"Chris ( Val )" <chris..._at_gmail.com> wrote:
>
> <QUERY>
>   SELECT *
> FROM schema.tbl_A a, schema.tbl_B b, schema.tbl_C c,
>        schema.tbl_D d, schema.tbl_E e, schema.tbl_F f,
>        schema.tbl_G g, schema.tbl_H h, schema.tbl_I i,
>        schema.tbl_J j, schema.tbl_K k, schema.tbl_L l,
>        schema.tbl_M m
>               WHERE  a.accountid = b.ID(+)
>                 AND  a.custom1id = c.ID(+)
>                 AND  a.custom2id = d.ID(+)
>                 AND  a.custom3id = e.ID
>                 AND  a.custom4id = f.ID(+)
>                 AND  a.entityid = g.ID(+)
>                 AND  a.icpid = h.ID(+)
>                 AND  a.parentid = i.ID(+)
>                 AND  a.periodid = j.ID(+)
>                 AND  a.scenarioid = k.ID(+)
>                 AND  a.valueid = l.ID(+)
>                 AND  a.viewid = m.ID(+)

Other comments notwithstanding, do you realise some of these outer joins are negated by...

>                 AND  d.label IN ('XXX', 'XXX')
>                 AND  e.label IN ('XXX', 'XXX')
>                 AND  f.label = 'X'
>                 AND  k.label = 'XXX'
>                 AND  l.label = 'XXX'
>                 AND  h.label = 'X'
>                 AND  m.label IN ('X', 'X');

You'll need additional (+)s on these clauses if you want a genuine outer join.

Try the alternative syntax...

  select distinct *
  from
    schema.tbl_a a

    left outer join schema.tbl_b b on b.id = a.accountid
    left outer join schema.tbl_c c on c.id = a.custom1id
    left outer join schema.tbl_d d
       on d.id = a.custom2id
      and d.label in ('XXX', 'XXX')
    join schema.tbl_e e
       on e.id = a.custom3id
      and e.label in ('XXX', 'XXX')
    left outer join schema.tbl_f f
       on f.id = a.custom4id
      and f.label = 'X'

    left outer join schema.tbl_g g on g.id = a.entityid     left outer join schema.tbl_h h

       on h.id = a.icpid
      and h.label = 'X'
    left outer join schema.tbl_i i on i.id = a.parentid

    left outer join schema.tbl_j j on j.id = a.periodid     left outer join schema.tbl_k k

       on k.id = a.scenarioid
      and k.label = 'XXX'
    left outer join schema.tbl_l l
       on l.id = a.valueid
      and l.label = 'XXX'
    left outer join schema.tbl_m m
       on m.id = a.viewid
      and m.label in ('X', 'X');

--
Peter

Received on Thu Feb 28 2008 - 16:16:59 CST