Problem Query Joins - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Problem Query Joins

Author

Topic

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-24 : 09:25:01

Hi,

Basic need is if a record has an Attribute of "Urgent", then the attributevalue should be displayed in the Urgent column. If the record has an attribute value of "closed", then the attributevalue must be displayed in the "Closed" column.

I have a query below. My problem is that among the results I am getting back, there are two records with the same RequesterID (one with a valid value in "Urgent" column and one with a value in "Closed" colum) My problem is that I need these two particular records to be displayed as one record.

Any ideas?

SELECT DISTINCT
r.RequesterID,
sr.ModifiedDate,
p.FirstName + ' ' + p.LastName AS RequesterName,
CASE WHEN sa.Attribute = 'Urgent' THEN sa.AttributeValue ELSE NULL END AS Urgent,
CASE WHEN sa.Attribute = 'Closed' THEN sa.AttributeValue ELSE NULL END AS Closed
FROM Requester AS r INNER JOIN SubRequester AS sr ON r.RequesterID = sr.RequesterID
INNER JOIN SubRequesterAttribute AS sa ON sr.SubRequesterID = sa.SubRequesterID
CROSS JOIN Personnel AS p
WHERE (r.UserID = p.ContractorID OR r.UserID = p.EmployeeID)
AND (sa.Attribute IN ('Urgent', 'Closed'))
GROUP BY r.RequesterID, sr.ModifiedDate, p.FirstName, p.LastName, sa.Attribute, sa.AttributeValue

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 10:03:35

You will need to remove the non-distinct columns from the GROUP BY clause. Perhaps use max(sr.ModifiedDate) and enclose your entire CASE expression(s) in a MAX(case when sa.Attribute = 'Urgent'... end) function.

Be One with the Optimizer
TG

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-24 : 10:23:36

quote:
Originally posted by TG

You will need to remove the non-distinct columns from the GROUP BY clause. Perhaps use max(sr.ModifiedDate) and enclose your entire CASE expression(s) in a MAX(case when sa.Attribute = 'Urgent'... end) function.

Be One with the Optimizer
TG

Didn't work.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 10:25:24

Post what you tried. As well as the reason it didn't work. Did you get an error? are you still getting multiple rows per requesterid ? what specifically didn't work?

Be One with the Optimizer
TG

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-24 : 10:49:13

SELECT DISTINCT
r.RequesterID,
sr.ModifiedDate,
p.FirstName + ' ' + p.LastName AS RequesterName,
MAX(CASE WHEN sa.Attribute = 'Urgent' THEN sa.AttributeValue ELSE NULL END AS Urgent,
CASE WHEN sa.Attribute = 'Closed' THEN sa.AttributeValue ELSE NULL END AS Closed)
FROM Requester AS r INNER JOIN SubRequester AS sr ON r.RequesterID = sr.RequesterID
INNER JOIN SubRequesterAttribute AS sa ON sr.SubRequesterID = sa.SubRequesterID
CROSS JOIN Personnel AS p
WHERE (r.UserID = p.ContractorID OR r.UserID = p.EmployeeID)
AND (sa.Attribute IN ('Urgent', 'Closed'))
GROUP BY r.RequesterID

Error: Incorrect syntax near ","
Msg 102, Level 16, State 1, Line 3

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 10:57:57

I meant that you should have each CASE expression in their own MAX function.

couple things:
- you don't need the DISTINCT key word when using GROUP BY.
- Is it possible that the same userid is both a contractor AND an employee? If not then you should probably just have [userid] in Personell along with an attribute that says the type of user (employee, contractor, etc). That way the JOINs become more efficient.

This statement assumes that for a given request there will only be one personell record (either emp or contractor):


SELECT r.RequesterID
       ,max(sr.ModifiedDate) as modifiedDate
       ,max(p.FirstName + ' ' + p.LastName) AS RequesterName
       ,max(CASE WHEN sa.Attribute = 'Urgent' THEN sa.AttributeValue ELSE NULL END) AS Urgent
       ,max(CASE WHEN sa.Attribute = 'Closed' THEN sa.AttributeValue ELSE NULL END) AS Closed
FROM   Requester AS r 
INNER JOIN SubRequester AS sr 
              ON r.RequesterID = sr.RequesterID
INNER JOIN SubRequesterAttribute AS sa 
              ON sr.SubRequesterID = sa.SubRequesterID
inner JOIN Personnel AS p
              on (p.ContractorID = r.userid OR p.EmployeeID = r.userid)
WHERE  sa.Attribute IN ('Urgent', 'Closed')
GROUP BY r.RequesterID

Be One with the Optimizer
TG

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-24 : 11:32:28

OMG
TG... YOU RULE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-24 : 11:33:25

quick question... I always thought MAX was for numeric values (like get me the max id field from requestID)
What does MAX imply in this scenario?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 11:38:00

In the same way that ORDER BY works for non-numeric data MAX will return the last alpha-numeric value.
Again, assuming there can only be one personel row per requesterid then you could include the name in the group by and remove the max...

Be One with the Optimizer
TG

CSharpNewbie
Starting Member

39 Posts

Posted - 2009-03-24 : 11:42:15

Thanks again bro. I have been working on this thing for about a week.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources