Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LEFT JOIN not returning anticipated results from Left table in query


LEFT JOIN not returning anticipated results from Left table in query

Author
Message
sjerromeharris
sjerromeharris
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8534 Visits: 18130
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
sjerromeharris
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
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
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16568 Visits: 17016
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8534 Visits: 18130
Would this give you the correct result?

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)
LEFT --This is the only change
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;




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
sjerromeharris
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
Brilliant, thanks very much.

I tweaked the query an extra little bit and made sure that there were no inner joins at all in the query, and hey presto I have a complete list.

Its the first time I've ever had need to use an outer join - I will re-read the article you posted the link to for next time.

Thanks once again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search