SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Hopefully a simple join question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

outjet
Starting Member

USA
3 Posts

Posted - 06/16/2014 :  10:31:16  Show Profile  Reply with Quote
Hello -

I am joining a table twice with separate aliases (Res & ResT). I'm hoping to show the ResT.ResourceDescription, but if there is no matching ResT.ResourceDescription, to still show the row, with NULL. However, when there IS a ResT.ResourceDescription, I end up with two rows, one NULL and one with the value.

SELECT DISTINCT
BKG.ID as BookingID
,SO.ID AS ServiceOrderID
,ResT.ResourceDescription as ResourceT
,STUFF((SELECT ','+ResourceDescription
		FROM tblResource RT2
		LEFT JOIN tblServiceOrderDetail SOD2 ON SOD2.ResourceID = RT2.ID
		WHERE SOD2.ServiceOrderID = SODT.ServiceOrderID AND RT2.GroupingID NOT IN (SELECT ID from tblCategoryGroup WHERE LEFT(Description,10) = 'Technician')
		FOR XML PATH('')),1,1,'') as Resources
FROM tblBooking BKG 
INNER JOIN tblServiceOrder SO ON SO.BookingID = Bkg.ID 
INNER JOIN tblServiceOrderDetail SOD ON SOD.ServiceOrderID = SO.ID 
INNER JOIN tblResource Res ON Res.ID = SOD.ResourceID 
LEFT JOIN tblServiceOrderDetail SODT ON SODT.ServiceOrderID = SO.ID 
LEFT JOIN tblResource ResT ON ResT.ID = SOD.ResourceID AND ResT.CATEGORYID = 3 AND REST.GroupingID IN (SELECT ID from tblCategoryGroup WHERE LEFT(Description,10) = 'Technician')


My results end up like this:

BookingID--|ServiceOrderID--|ResourceT-----|Resources
123--------|333-------------|NULL----------|Item1, Item2, etc
123--------|333-------------|Technician A--|Item1, Item2, etc
456--------|444-------------|NULL----------|Item1, Item2, etc
456--------|444-------------|Technician A--|Item1, Item2, etc
789--------|555-------------|NULL----------|Item1, Item2, etc


I am hoping to get them to show up like this:

BookingID--|ServiceOrderID--|ResourceT-----|Resources
123--------|333-------------|Technician A--|Item1, Item2, etc
456--------|444-------------|Technician A--|Item1, Item2, etc
789--------|555-------------|NULL----------|Item1, Item2, etc


Any ideas would be greatly appreciated!

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 06/16/2014 :  10:40:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why are you joing the Resource table twice?
One over ResourceID and then again, with an outer join, over ResourceID and some more criterias (Category 3 and some GroupingID subquery thingy).



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 06/16/2014 :  10:41:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The easiest way to change the result is to change the LEFT JOIN to an INNER JOIN for tblResource.
But then again, I take no responsibility since I have no idea of the business rules.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

outjet
Starting Member

USA
3 Posts

Posted - 06/16/2014 :  11:07:22  Show Profile  Reply with Quote
Sure thing - thanks for helping me look into this -- some more info based on your questions -- The Resources table has two kinds of resources -- one are "Items", and the other are "Technicians". I am re-joining the table, so I can show the Technician resource separately from the stuffed Items list. If I do an inner join, then I end up excluding records where no Technician has been ordered yet.

Edited by - outjet on 06/16/2014 11:07:52
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 06/16/2014 :  15:17:36  Show Profile  Reply with Quote
What do you get when you comment out the INNER JOIN to Res ?
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 06/16/2014 :  15:25:56  Show Profile  Reply with Quote
Also, as a note, I find that developers often use DISTINCT when they are getting duplicates they don't want and don't understand where they are coming from. I don't know if this is the case with your query, but my advice is to only use DISTINCT when you know exactly why you have multiple records and cannot write the query any other way to avoid them. From looking at your column list, table list and JOIN criteria, my hypothesis is that this query falls into the former. Make sure you understand your schema, foreign keys, and cardinality. If you don't, discover it by starting with one table via a SELECT *, then adding in one JOIN at a time and executing your query to determine where the duplicates are coming from and why. Following this practice will improve both your query performance as well as your overall understanding of the data model and subsequent ability to write queries against it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000