Problem Query

Question

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

Answer 1

You will need to join to your sub requester attribute table to the query twice. One with the attribute of Urgent and one with the attribute of Close.

You will need to LEFT join to these for the instances where they may be null and then reference each of the tables in your SELECT to show the relevent attribute.

I also wouldn't reccomend the cross join. You should perform your "OR" join on the personnel table in the FROM clause rather than doing a cross join and filtering in the WHERE clause.

EDIT: Sorry, my first response was a bit rushed. Have now had a chance to look further. Due to the sub requester and the sub requester attribute both being duplicates you need to split them both up into a subquery. Also, your modified date could be different for both values. So i've doubled that up. This is completely untested, and by no means the "optimum" solution. It's quite tricky to write the query without the actual database to check against. Hopefully it will explain what I meant though.

SELECT
 r.RequesterID,
 p.FirstName + ' ' + p.LastName AS RequesterName,
 sra1.ModifiedDate as UrgentModifiedDate,
 sra1.AttributeValue as Urgent,
 sra2.ModifiedDate as ClosedModifiedDate,
 sra2.AttributeValue as Closed
FROM
 Personnel AS p
INNER JOIN
 Requester AS r 
ON
(
 r.UserID = p.ContractorID
OR
 r.UserID = p.EmployeeID
)
LEFT OUTER JOIN
(
 SELECT
  sr1.RequesterID,
  sr1.ModifiedDate,
  sa1.Attribute,
  sa1.AttributeValue
 FROM
  SubRequester AS sr1
 INNER JOIN
  SubRequesterAttribute AS sa1
 ON
  sr1.SubRequesterID = sa1.SubRequesterID
 AND
  sa1.Attribute = 'Urgent'
) sra1
ON
 sra1.RequesterID = r.RequesterID
LEFT OUTER JOIN
(
 SELECT
  sr2.RequesterID,
  sr2.ModifiedDate,
  sa2.Attribute,
  sa2.AttributeValue
 FROM
  SubRequester AS sr2
 INNER JOIN
  SubRequesterAttribute AS sa2
 ON
  sr2.SubRequesterID = sa2.SubRequesterID
 AND
  sa2.Attribute = 'Closed'
) sra1
ON
 sra2.RequesterID = r.RequesterID

SECOND EDIT: My last edit was that there were multiple SubRequesters as well as multiple Attribute, from your last comment you want to show all SubRequesters and the two relevent attributes? You can achieve this as follows.

SELECT
    r.RequesterID,
    p.FirstName + ' ' + p.LastName AS RequesterName,
    sr.ModifiedDate,
    sa1.AttributeValue as Urgent,
    sa2.AttributeValue as Closed
FROM
    Personnel AS p
INNER JOIN
    Requester AS r 
ON
(
    r.UserID = p.ContractorID
OR
    r.UserID = p.EmployeeID
)
INNER JOI N
    SubRequester as sr
ON
    sr.RequesterID = r.RequesterID
LEFT OUTER JOIN
    SubRequesterAttribute AS sa1
ON
    sa1.SubRequesterID = sr.SubRequesterID
AND
    sa1.Attribute = 'Urgent'
LEFT OUTER JOIN
    SubRequesterAttribute AS sa2
ON
    sa2.SubRequesterID = sr.SubRequesterID
AND
    sa2.Attribute = 'Closed'

Answer 2

Generally, if you have multiple rows and want to collapse them into one, GROUP BY is the basic tool to achieve that. It looks like you tried to go in that direction but didn't quite get there. What you want to do is group by the expressions that are duplicated between the rows, and apply group functions to the other expressions that will eliminate the NULL values. I used MIN in the example below but you could just as easily use MAX; the point is that since at most one of the rows will have a value for that expression, that value is both the minimum and the maximum.

SELECT
   r.RequesterID,
   sr.ModifiedDate,
   p.FirstName + ' ' + p.LastName AS RequesterName,
   MIN(
   CASE
     WHEN sa.Attribute = 'Urgent' THEN sa.AttributeValue
     ELSE NULL
   END
   ) AS Urgent,
   MIN(
   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