Query several EAV attributes in separate columns

Answer 1

I'm not 100% sure that I understand, but I think this satisfies what you're asking for. Note that this was assuming that the database is MySQL, you didn't specify. The syntax of the IF() and CONCAT() may be slightly different if it's something else.

EDIT: updated query as per Csharp's "answer" below. MAX-ing the name is a bit of a hack.

SELECT t.TicketID,
    MAX(CONCAT(p.FirstName, ' ', p.LastName)) AS RequesterFullName,
    MAX(IF(a.Attribute = 'Urgent', a.AttributeValue, NULL)) AS UrgentPriorityID,
    MAX(IF(a.Attribute = 'Medium', a.AttributeValue, NULL)) AS MediumPriorityID,
    MAX(IF(a.Attribute = 'Low', a.AttributeValue, NULL)) AS LowPriorityID
FROM tblTicketIssues AS t
    LEFT JOIN tblPersonnelProfile AS p ON p.PersonnelID = t.RequesterID
    LEFT JOIN tblTicketAttribute AS a ON a.TicketID = t.TicketID
WHERE a.Attribute IN ('Urgent', 'Medium', 'Low')
GROUP BY t.TicketID;

Answer 2

I have no idea why you want to do it this way, but here goes (assuming SQL Server):

 SELECT TicketID, FirstName + ' ' + LastName AS RequestFullName,
 CASE WHEN Attribute = "Low" THEN AttributeValue ELSE "" END AS LowPriorityID,
 CASE WHEN Attribute = "Medium" THEN AttributeValue ELSE "" END AS MediumPriorityID,
 CASE WHEN Attribute = "Urgent" THEN AttributeValue ELSE "" END AS UrgentPriorityID
 FROM ...
 WHERE Attribute IN ("Urgent", "Low", "Medium")

But that seems like a bizarre way to do things to me.

Can you clarify if the relationship between tbTicketIssues and tbTicketAttributes is one-to-one, or one-to-many?

Answer 3

One relatively straightforward way to do something like this would be to just union three queries together. I suspect that what you're looking for would be something like this:

SELECT i.TicketID, a.FirstName, pp.AttributeValue AS UrgentPriorityID, null AS MediumPriorityID, null AS LowPrioritytID
FROM tblTicketIssues i, tblTicketAttribute a, tblPersonnelProfile pp
WHERE i.RequesterID = a.PersonnelID
AND i.TicketID = pp.TicketID
AND pp.Attribute = "Urgent"
UNION
SELECT i.TicketID, a.FirstName, null AS UrgentPriorityID, pp.AttributeValue AS MediumPriorityID, null AS LowPrioritytID
FROM tblTicketIssues i, tblTicketAttribute a, tblPersonnelProfile pp
WHERE i.RequesterID = a.PersonnelID
AND i.TicketID = pp.TicketID
AND pp.Attribute = "Medium"
UNION
SELECT i.TicketID, a.FirstName, null AS UrgentPriorityID, null AS MediumPriorityID, pp.AttributeValue AS LowPrioritytID
FROM tblTicketIssues i, tblTicketAttribute a, tblPersonnelProfile pp
WHERE i.RequesterID = a.PersonnelID
AND i.TicketID = pp.TicketID
AND pp.Attribute = "Low"

(this is SQL Server btw, though I doubt it would be much different on pretty much any RDBMS)

There are doubtless "slicker" ways that this could be done, but I like this way because it's very straightforward when reading it.

Answer 4

@ Dana, it is a one-to-many relationship (since the ticket can include a subticket)

@ Chad, your query works fine, except that I have one record showing twice (one has a ticket as urgent, one record has a ticket low) How can I show them both on the same row?

Answer 5

The database design uses the Entity-Attribute-Value pattern for the tblTicketAttribute table. The difficulty you faced trying to get this rather ordinary query result shows how EAV causes a lot of problems.

The solution by @Chad Birch is one way to get the result. Here's another way to get the result you want:

SELECT t.TicketID,
    CONCAT(p.FirstName, ' ', p.LastName) AS RequesterFullName,
    a1.AttributeValue AS UrgentPriorityID,
    a2.AttributeValue AS MediumPriorityID,
    a3.AttributeValue AS LowPriorityID
FROM tblTicketIssues AS t
  JOIN tblPersonnelProfile AS p ON (p.PersonnelID = t.RequesterID)
  LEFT JOIN tblTicketAttribute AS a1 
    ON (a1.TicketID = t.TicketID AND a1.Attribute = 'Urgent')
  LEFT JOIN tblTicketAttribute AS a2 
    ON (a2.TicketID = t.TicketID AND a2.Attribute = 'Medium')
  LEFT JOIN tblTicketAttribute AS a3 
    ON (a3.TicketID = t.TicketID AND a3.Attribute = 'Low');

This solution uses no GROUP BY clause, but it does require a separate JOIN for each attribute you want to retrieve.

Yet another solution is to fetch attributes on multiple rows of the result set:

SELECT t.TicketID,
    CONCAT(p.FirstName, ' ', p.LastName) AS RequesterFullName,
    a.AttributeValue AS AnyPriorityID
FROM tblTicketIssues AS t
  JOIN tblPersonnelProfile AS p ON (p.PersonnelID = t.RequesterID)
  LEFT JOIN tblTicketAttribute AS a 
    ON (a1.TicketID = t.TicketID AND a.Attribute IN ('Urgent', 'Medium', 'Low'));

This solution scales better as an SQL query, because you don't have to add more JOIN clauses as you fetch more attributes. But it does mean you have to do some post-processing of the result set in your application code, to get it into the format you want.