Help Multi table Query

Help Multi table Query

Post by JD » Mon, 09 Jun 2003 00:36:39



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

 
 
 

Help Multi table Query

Post by JD » Mon, 09 Jun 2003 00:44:23


Sorry , Alt-S'd it too soon....and left out the group by....

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


 
 
 

Help Multi table Query

Post by Steve Kas » Mon, 09 Jun 2003 02:18:24


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


>Sorry , Alt-S'd it too soon....and left out the group by....

>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.

>>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

 
 
 

1. HELP PLEASE: URGENT: Need help forming a multi table query

Hello,

I'm using SQL SERVER 7.0. I'm very new to Multi-Table querying, and
can't seem to figure this one out. Any help would be appreciated.

I have 2 tables. one is called RATES and one is called BILLING

In the BILLING table, there are 2 columns, one is COUNTRY and
the other is ACTUAL.  In the RATES table, there are two columns
COUNTRY (which you would use to match the rate to) and RATE.

This is for an ASP page. It has to go row by row of the BILLING
table and calculate the total of a phone call, by multiplying the
ACTUAL of BILLING column with the RATE for that COUNTRY.
It sounds simple, but I get strange (wrong) results everytime.

Any help would be appreciated.  Thanks in advance.

Ayaz Asif

2. Reference request

3. help with multi-table query

4. Transferring objects from Sybase (UNIX) to SQL Server 6.5

5. Help please with multi-table query

6. US-CA-ORACLE FINANCIALS / SYSTEMS ANALYST

7. help with multi-table query

8. Oracle 7.x Tech help !!

9. Need help trying to set up Query across multi tables

10. HELP: URGENT: MULTI-TABLE Query (SQL7)

11. Empty tables in a multi-tables query