Hopefully a simple join question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Hopefully a simple join question

Author

Topic

outjet
Starting Member

3 Posts

Posted - 2014-06-16 : 10:31:16

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

30421 Posts

Posted - 2014-06-16 : 10:40:46

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-16 : 10:41:54

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

outjet
Starting Member

3 Posts

Posted - 2014-06-16 : 11:07:22

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.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-06-16 : 15:17:36

What do you get when you comment out the INNER JOIN to Res ?

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-06-16 : 15:25:56

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources