Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Left Joins w/more than 2 tables
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',
AND a.[HistCustNo] = al.[HistCustNo] AND a.[HistItemNo]= al.[HistItemNo] AND a.[HistType]= al.[HistType]
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',
AND a.[HistCustNo] *= al.[HistCustNo] AND a.[HistItemNo] *= al.[HistItemNo] AND a.[HistType] *= al.[HistType]