LEFT JOIN not returning anticipated results from Left table in query

Question

Post reply

LEFT JOIN not returning anticipated results from Left table in query

sjerromeharris

SSC-Addicted

Points: 434
More actions
March 20, 2014 at 10:26 am

#291087

I have a query that joins three tables:
Users (Table)
Code (int)
FeeEarner (bit)
UserTypeRef (int)
Matters (Table)
entityref (varchar)
number (int)
feeearnerref (varchar)
created (date)
Usr_int1 (Table)
Estimated_total_fee (int)
Matters are assigned to members of staff. I want to list how many matters have been created in the last week for each member of staff, and the estimated fee we will charge on those new matters. I'm using the COUNT function to count the number of matters for each member of staff, and ISNULL(SUM) to calculate the estimated fees whilst allowing for no fee being entered.
The staff must be a certain type of employee (users.feeearner = 1). They must also be a member of staff who hasn't left (users.usertyperef <>7), and the matter created since a specific date.
What I'm hoping to see is all the active members of staff listed, regardless of whether a new matter has been assigned to them, for example
AAW 3 300
AMW 0 0
AWM 2 5000
so I have used Users INNER JOIN Matters
but what is actually being returned is only the members of staff who have had a matter allocated to them, for example
AAW 3 300
AWM 2 5000
I'm not sure what I'm doing wrong here? Am I using the wrong type of join?
SELECT
Users.Code as FE,
COUNT(Matters.FeeEarnerRef) as No_of_Matters,
ISNULL(SUM(Usr_Int1.Estimated_total_fee),0) as Fee_Estimate
FROM
(Users LEFT JOIN Matters ON Users.Code = Matters.FeeEarnerRef)
INNER JOIN Usr_Int1 ON (Matters.Number = Usr_Int1.MatterNo) AND (Matters.EntityRef = Usr_Int1.EntityRef)
WHERE
(created >'2014-03-16') and (users.feeearner = 1)
GROUP BY
code, usertyperef
HAVING
(usertyperef <> 7)
ORDER BY
code
Any help gratefully received.
Thanks
Sarah

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Your WHERE clause is converting your LEFT JOIN into an INNER JOIN.

You need to change your query a little bit.

Here's a nice article on the subject: http://www.sqlservercentral.com/articles/T-SQL/93039/

SELECT Users.Code AS FE

,COUNT(Matters.FeeEarnerRef) AS No_of_Matters

,ISNULL(SUM(Usr_Int1.Estimated_total_fee), 0) AS Fee_Estimate

FROM Users

LEFT JOIN Matters ON Users.Code = Matters.FeeEarnerRef

AND created > '2014-03-16'

INNER JOIN Usr_Int1 ON (Matters.Number = Usr_Int1.MatterNo)

AND (Matters.EntityRef = Usr_Int1.EntityRef)

WHERE (users.feeearner = 1)

AND usertyperef <> 7

GROUP BY code

,usertyperef

ORDER BY code

And you don't need the HAVING clause, it could easily go into the WHERE clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sjerromeharris SSC-Addicted Points: 434 More actions · Answer 2

Thanks for your post, and the link to the article.

I've tried your suggestion, but its not making much difference to the results that are being returned. I'm expecting around 58 rows but I'm only getting around 40.

Is there anything else I can try? (I've added a couple of extra columns to the query)

SELECT

Users.Department as Dept,

CaseTypes.Description as Case_Type,

Users.Code as FE,

COUNT(Matters.FeeEarnerRef) as No_of_Matters,

ISNULL(SUM(Usr_Int1.Estimated_total_fee),0) as Fee_Estimate

FROM

((Users LEFT JOIN Matters ON Users.Code = Matters.FeeEarnerRef and (Matters.Created >= GetDate() - 7 AND Matters.Created <= GetDate())

INNER JOIN CaseTypes ON Matters.CaseTypeRef = CaseTypes.Code)

INNER JOIN Usr_Int1 ON (Matters.Number = Usr_Int1.MatterNo) AND (Matters.EntityRef = Usr_Int1.EntityRef))

WHERE

((usertyperef =1) or (usertyperef = 2))

GROUP BY

users.code, usertyperef, users.department, casetypes.description, users.department, casetyperef, users.feeearner

ORDER BY

users.department, casetyperef, users.code;

Thank you

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

sjerromeharris (3/24/2014)
Thanks for your post, and the link to the article.
I've tried your suggestion, but its not making much difference to the results that are being returned. I'm expecting around 58 rows but I'm only getting around 40.
Is there anything else I can try? (I've added a couple of extra columns to the query)
SELECT
Users.Department as Dept,
CaseTypes.Description as Case_Type,
Users.Code as FE,
COUNT(Matters.FeeEarnerRef) as No_of_Matters,
ISNULL(SUM(Usr_Int1.Estimated_total_fee),0) as Fee_Estimate
FROM
((Users LEFT JOIN Matters ON Users.Code = Matters.FeeEarnerRef and (Matters.Created >= GetDate() - 7 AND Matters.Created <= GetDate())
INNER JOIN CaseTypes ON Matters.CaseTypeRef = CaseTypes.Code)
INNER JOIN Usr_Int1 ON (Matters.Number = Usr_Int1.MatterNo) AND (Matters.EntityRef = Usr_Int1.EntityRef))
WHERE
((usertyperef =1) or (usertyperef = 2))
GROUP BY
users.code, usertyperef, users.department, casetypes.description, users.department, casetyperef, users.feeearner
ORDER BY
users.department, casetyperef, users.code;
Thank you

It is because you have an INNER join to Usr_Int1 using a value from Matters. This basically converts this to an INNER join on Matters. Change that to a LEFT and I think you are pretty close.

SELECT Users.Code AS FE

,COUNT(Matters.FeeEarnerRef) AS No_of_Matters

,ISNULL(SUM(Usr_Int1.Estimated_total_fee), 0) AS Fee_Estimate

FROM Users

LEFT JOIN Matters ON Users.Code = Matters.FeeEarnerRef

AND created > '2014-03-16'

LEFT JOIN Usr_Int1 ON (Matters.Number = Usr_Int1.MatterNo)

AND (Matters.EntityRef = Usr_Int1.EntityRef)

WHERE (users.feeearner = 1)

AND usertyperef <> 7

GROUP BY code

,usertyperef

ORDER BY code

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Would this give you the correct result?