Left Joins w/more than 2 tables

From: Scott <scott_at_clevelanddata.com>
Date: 2000/07/07
Message-ID: <39660afe$0$9674_at_wodc7nh6.news.uu.net>#1/1

Hello:

I have a 3 table join that I'd like to see data from the laslsmen table that doesnt have matching data from the other to tables... using the left join's it just shows the 1-to-1 results

I try using the *= in where clauses and get this: Query contains an outer-join request that is not permitted.

I simply need to see the salesman even if theres no activity in their 2-year history tables....

Also Id like to avoid using views or temporary tables, or sp's... just a straight query.

Thanks,
Scott Kramer
scott_at_clevelanddata.com

join:

select s.salesmanid,s.[name],s.[groupcode], SUM(a.histamt1+a.histamt2+a.histamt3+a.histamt4+a.histamt5+a.histamt6+a.hist amt7) as 'YTD',

SUM(a.histamt7) as 'MTD',
SUM(al.histamt7) as 'LYMTD',
SUM(al.histamt1+al.histamt2+al.histamt3+al.histamt4+al.histamt5+al.histamt6+

al.histamt7) as 'LYTD',
SUM(al.HistAmt1 + al.HistAmt2 + al.HistAmt3 + al.HistAmt4 + al.HistAmt5 + al.HistAmt6 + al.HistAmt7 + al.HistAmt8 + al.HistAmt9 + al.HistAmt10 + al.HistAmt11 + al.HistAmt12) as 'LY'
FROM laslsmen s
LEFT JOIN arhist_0_f a
ON s.salesmanid = a.custsalesman
LEFT JOIN arhist_1_f al
ON a.[HistRecordType] = al.[HistRecordType]

 AND a.[HistCustNo] = al.[HistCustNo]
 AND a.[HistItemNo]= al.[HistItemNo]
 AND a.[HistType]= al.[HistType]

where a.histrecordtype = 'C'
 and groupcode = '100'
group by s.salesmanid,s.[name],s.[groupcode]

where clause:

select s.salesmanid,s.[name],s.[groupcode], SUM(a.histamt1+a.histamt2+a.histamt3+a.histamt4+a.histamt5+a.histamt6+a.hist amt7) as 'YTD',

SUM(a.histamt7) as 'MTD',
SUM(al.histamt7) as 'LYMTD',
SUM(al.histamt1+al.histamt2+al.histamt3+al.histamt4+al.histamt5+al.histamt6+

al.histamt7) as 'LYTD',
SUM(al.HistAmt1 + al.HistAmt2 + al.HistAmt3 + al.HistAmt4 + al.HistAmt5 + al.HistAmt6 + al.HistAmt7 + al.HistAmt8 + al.HistAmt9 + al.HistAmt10 + al.HistAmt11 + al.HistAmt12) as 'LY'
FROM laslsmen s , arhist_0_f a, arhist_1_f al  where s.[salesmanid] *= a.[custsalesman] and a.[HistRecordType] *= al.[HistRecordType]

 AND a.[HistCustNo] *= al.[HistCustNo]
 AND a.[HistItemNo] *= al.[HistItemNo]
 AND a.[HistType] *= al.[HistType]

and a.histrecordtype = 'C'
 --and groupcode = '100'
 group by s.salesmanid,s.[name],s.[groupcode] Received on Fri Jul 07 2000 - 00:00:00 CEST