Help with Cross Join - 3 Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Cross Join - 3 Tables

Author

Topic

batcater98
Starting Member

22 Posts

Posted - 2008-02-26 : 16:34:12

I am wanting to show Total Quantity of Presentations2 for each EmpVol and TypeofPresentation, even if there are no Presentations done in TypeofPresentation or if an Employee did not do any Presentations.

I am close with the query below - but not getting back exactly what I want - Ideas.

SELECT ISNULL(Presentations2.Quantity, 0) AS Quantity, TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER
FROM Presentations2 INNER JOIN
EmpVol ON Presentations2.ID = EmpVol.ID RIGHT OUTER JOIN
TypeofPresentation ON Presentations2.TypePresINT = TypeofPresentation.TypePresINT CROSS JOIN
EmpVol AS EmpVol_1 CROSS JOIN
EmpVol AS EmpVol_2
GROUP BY TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER, ISNULL(Presentations2.Quantity, 0)
HAVING (EmpVol_2.FSSTVOLUNTEER = N'FSST')

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 23:14:52

Can you post your table structures,sample data & expected o/p? That will make it clear for people who are trying to help. Thanks

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-26 : 23:52:57

try replacing INNER JOIN, RIGHT OUTER JOIN's with LEFT JOIN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources