Problem Query

Question

I am facing issues trying to write a query.

My tables are laid out as follows:

tblTicketIssues
TicketID | RequesterID

tblPersonnelProfile
PersonnelID | FirstName | LastName

tblTicketAttribute
TicketID | Attribute | AttributeValue

I have to display the following fields:

TicketID, 
RequesterFullName, 
UrgentPriorityID, 
MediumPriorityID, 
LowPrioritytID

This is the part that is challenging:

If tblTicketAttribute.Attribute= "Urgent" then the value from tblTicketAttribute.AttributeValue is displayed in UrgentPriority column

If tblTicketAttribute.Attribute= "Medium" then the value from tblTicketAttribute.AttributeValue is displayed in MediumPriority column

If tblTicketAttribute.Attribute= "Low" then the value from tblTicketAttribute.AttributeValue is displayed in LowPriority column

The values in tblTicketAttribute.Attribute include "Urgent", "Medium", "Low", "Over30", "Over60", "Over90", "Closed"

How can I do this?

Answer 1

Check out the CASE statement.

select
 ticketID
 ,Lastname +', '+firstname
 ,CASE attribute 
      WHEN 'Urgent' THEN attributeValue 
      ELSE '' 
 END as UrgentPriorityID
 ,CASE attribute 
      WHEN 'Medium' THEN attributeValue 
      ELSE '' 
 END as MediumPriorityID
 ,CASE attribute 
      WHEN 'Low' THEN attributeValue 
      ELSE '' 
 END as LowPrioritytID

from
 ...

Answer 2

I've made the assumption that RequestorID is the foreign key for the PersonnelID primary key in tblPersonnelProfile. This should do it for SQL Server

SELECT
    issues.TicketID, 
    personnel.FirstName + ' ' + personnel.LastName AS RequesterFullName, 
    CASE WHEN attribute.Attribute = 'Urgent' THEN attribute.AttributeValue ELSE NULL END AS UrgentPriorityID, 
    CASE WHEN attribute.Attribute = 'Medium' THEN attribute.AttributeValue ELSE NULL END AS MediumPriorityID, 
    CASE WHEN attribute.Attribute = 'Low' THEN attribute.AttributeValue ELSE NULL END AS LowPrioritytID
FROM
    tblTicketIssues issues
INNER JOIN
    tblPersonnelProfile personnel
ON
    issues.RequestorID = personnel.PersonnelID
INNER JOIN
    tblTicketAttribute attribute
ON
    issues.TicketID = attribute.TicketID

Answer 3

Using the CASE statement as jms or Russ have done is probably the much better way to go, but you could also use sub-selects to solve the problem:

SELECT  ti.TicketID,
        pp.FirstName || ' ' || pp.LastName AS RequesterFullName,
        (SELECT ta.AttributeValue FROM tblTicketAttribute AS ta WHERE ta.Attribute = 'Urgent' AND ta.TicketID = ti.TicketID) AS UrgentPriorityID,
        (SELECT ta.AttributeValue FROM tblTicketAttribute AS ta WHERE ta.Attribute = 'Medium' AND ta.TicketID = ti.TicketID) AS MediumPriorityID,
        (SELECT ta.AttributeValue FROM tblTicketAttribute AS ta WHERE ta.Attribute = 'Low' AND ta.TicketID = ti.TicketID) AS LowPriorityID
FROM    tblTicketIssues AS ti
        INNER JOIN tblPersonnelProfile AS pp ON ti.RequestorID = pp.PersonnelID;

I suspect the CASE would be much faster, but you never know until you measure, and sometimes the sub-select approach is your only option. You'll automatically get NULLs in the sub-select columns if the WHERE predicate is false.

Also, you get to see a whole 'nuther way to format SQL queries, as well as the more Oracle-ish concatenation operator.

I should also mention that the sub-select won't work if the TicketID column in tblTicketAttribute is not unique, but the other options may give wonky results too, if that's the case.