Missing record in a complex SELECT FULL JOIN statement

I created a SQL statement that should return the number of appointments receive by all salesmen. I work wi开发者_如何学Cth 3 tables, Contract, Salesmen and Appointment, and I need to show how many appointments was received by each salesmen.

My problem is that although I use a Full Join the result doesn't show people who didn't receive any appointments. I found that there is a problem about constraint.

I took a look to Except, Intercept and Union option but none of those could solve my problem. Which other way could I use to get the full list of reps having or not received some appointments?

There is an example of the statement I used:

SELECT C.RepID, COUNT(A.AppID) AS AppAttrib, C.AppointmentPurchased, S.Name, S.FirstName
FROM Repartition.dbo.Contract C 
FULL JOIN Repartition.DBO.Appointment A
ON C.RepID = A.RepID
LEFT JOIN Repartition.DBO.Salesmen S
ON S.RepID = C.RepID
GROUP BY C.RepID, V.Nom, S.Name, S.FirstName

Thanks for your help,

Antenor

Not knowing your table structure in detail, I'm just guessing here - but I think your query starts at the wrong place - you should start with the Salesmen table, and go from there. So basically, select those columns from the Salesmen table that you need, and then join in the other tables as needed.

Something like this:

SELECT 
     s.RepID, S.Name, S.FirstName, 
     COUNT(A.AppID) AS AppAttrib, 
     C.AppointmentPurchased 
FROM  
     Repartition.dbo.Salesmen s
LEFT OUTER JOIN 
     Repartition.dbo.Contract c ON s.RepID = c.RepID
LEFT OUTER JOIN
     Repartition.dbo.Appointment a ON s.RepID = a.RepID
GROUP BY 
     s.RepID, s.Name, s.FirstName