Very Computer

I want only single max date values for cs, ch and ca.

I used join on similar to my where, but got the same results.

select
    c1.accountno
    ,c1.lastdate
    ,c1.lastuser
    ,c2.lastconton
    ,c2.ulastdate
    ,max(cs.lastdate)
    ,cs.lastuser
    ,max(ch.lastdate)
    ,ch.lastuser
    ,max(ca.lastdate)
    ,ca.lastuser

from contact1 c1 ,contact2 c2 ,contS cs ,contH ch ,contA ca

where c1.accountno = c2.accountno and c1.accountno = cs.accountno
            and c1.accountno = ch.accountno and c1.accountno = ca.accountno

I'm getting a row for each instance of a record in the 1:many tables, but I only
want one row for each accountno.

I can do this with c1 and c2, but as soon as I include the 1:many tables I get
the additional rows.

Quote:> Tables contact1 and contact2 are 1:1, cs, ch and ca are 1:many.

> I want only single max date values for cs, ch and ca.

> I used join on similar to my where, but got the same results.

> select
>     c1.accountno
>     ,c1.lastdate
>     ,c1.lastuser
>     ,c2.lastconton
>     ,c2.ulastdate
>     ,max(cs.lastdate)
>     ,cs.lastuser
>     ,max(ch.lastdate)
>     ,ch.lastuser
>     ,max(ca.lastdate)
>     ,ca.lastuser

> from contact1 c1 ,contact2 c2 ,contS cs ,contH ch ,contA ca

> where c1.accountno = c2.accountno and c1.accountno = cs.accountno
>             and c1.accountno = ch.accountno and c1.accountno = ca.accountno

group by
>     c1.accountno
>     ,c1.lastdate
>     ,c1.lastuser
>     ,c2.lastconton
>     ,c2.ulastdate
>     ,cs.lastdate
>     ,cs.lastuser
>     ,ch.lastdate
>     ,ch.lastuser
>     ,ca.lastdate
>     ,ca.lastuser

If you only want one row for each account number in your result,
what values of c1.lastdate, c1.lastuser, c2.lastconton, c2.ulastdate,
cs.lastuser, ch.lastuser, and ca.lastuser to be in that row?

If you want the values that are in the same rows as the max dates
you are selecting, you can try this [untested]

select
  c1.accountno,
  c1.lastdate,
  c2.lastconton,
  c2.ulastdate,
  cs.lastdate,
  cs.lastuser,
  ch.lastdate,
  ch.lastuser,
  ca.lastdate,
  ca.lastuser
from <your tables>
where <your where clause>
and not exists (
  select * from contact1 c1X, contact2 c2X, contS csX, contH csH, contA csA
  where <your where clause with the X aliases>
  and  c1.accountno = c1X.accountno
  and (cs.lastdate < csX.lastdate or ch.lastdate < chX.lastdate or
ca.lastdate < caX.lastdate)
)

In other words, select everything from the join,
unless the join contained a later date in any of the max
columns.

I suspect this won't be efficient, and there are other ways
of writing this.  The following might work, but I'm a little
less sure about it:

select
  c1.accountno,
  c1.lastdate,
  c2.lastconton,
  c2.ulastdate,
  cs.lastdate,
  cs.lastuser,
  ch.lastdate,
  ch.lastuser,
  ca.lastdate,
  ca.lastuser
from (
  select accountno, lastdate from contact1
) c1, (
  select accountno, lastconton, ulastdate from contact2
) c2, (
  select accountno, lastdate, lastuser from contS cs1
  where not exists (
    select * from contS cs2
    where cs2.accountno = cs1.accountno
    and cs2.lastdate > cs1.lastdate
  )
) cS, (
  select accountno, lastdate, lastuser from contH ch1
  where not exists (
    select * from contH ch2
    where ch2.accountno = ch1.accountno
    and ch2.lastdate > ch1.lastdate
  )
) cH, (
  select accountno, lastdate, lastuser from contA ca1
  where not exists (
    select * from contA ca2
    where ca2.accountno = ca1.accountno
    and ca2.lastdate > ca1.lastdate
  )
) cA
where <your where clause>

Steve Kass
Drew University