INNER and OUTER JOIN in same query 1

[organicg]

I have four DB tables.
Employee table has ID, Name
EmployeeGroup has EmployeeID, GroupID (Employees are in a group)
TixEventSignups table has EmployeeID, EventID, NumOfTix(Employees get tix to an event)
Event table has EventID, Name

I need the Employee Name and NumOfTix requested for their group for an event, but I need all the Names in the group even if they have no rows in TixEventSignups.
Seems easy, what am I doing wrong?
Thanks

Select e.LastName, tes.Requested
FROM Employee e
INNER JOIN TixGroups_Emp tge ON e.EmployeeID = tge.Employee
LEFT JOIN TixEvent_Signups tes ON tes.EmpID = e.EmployeeID
where tge.[Group]=2
AND tes.EventID = 408

 

[markros]

Select e.LastName, tes.RequestedFROM Employee e
INNER JOIN TixGroups_Emp tge ON e.EmployeeID = tge.Employee LEFT JOIN TixEvent_Signups tes ON tes.EmpID = e.EmployeeID
AND tes.EventID = 408
where tge.[Group]=2

Move your condition on tes into JOIN clause.

 

[markros]

If you put condition on tes (table used in the right side of the LEFT JOIN), you will transform your JOINS into INNER JOINs by doing this. If you want to respect the LEFT JOIN, then the condition MUST BE in the JOIN and not in where.

Simple rule - I broke it 3 times before learning by heart.

 

[organicg]

I thought all conditions needed to come after 'WHERE'. Seems strange to have 'AND' condition first, but thanks so much! I guess I'll read the syntax documentation more closely.

 

[Emtucifor]

The AND is part of the ON clause. This is why I'm such a stickler for formatting...

SELECT
   e.LastName,
   tes.Requested
FROM Employee e
INNER JOIN
   TixGroups_Emp tge ON e.EmployeeID = tge.Employee
   LEFT JOIN TixEvent_Signups tes
      ON tes.EmpID = e.EmployeeID
      AND tes.EventID = 408
WHERE tge.[Group] = 2

I hope this makes it clearer that the condition is determining how rows are LEFT JOINed to the previous table.

 

[gmmastros]

For additional explanation of this issue....

thread183-1504081


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom