Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LEFT JOIN not returning anticipated results from Left table in query Expand / Collapse
Author
Message
Posted Thursday, March 20, 2014 10:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 8:50 AM
Points: 16, Visits: 48
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
Post #1553120
Posted Thursday, March 20, 2014 10:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 3,771, Visits: 8,463
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1553130
Posted Monday, March 24, 2014 8:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 8:50 AM
Points: 16, Visits: 48
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
Post #1554063
Posted Monday, March 24, 2014 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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)
Post #1554070
Posted Monday, March 24, 2014 8:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 3,771, Visits: 8,463
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554073
Posted Monday, March 24, 2014 11:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 8:50 AM
Points: 16, Visits: 48
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.
Post #1554146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse