Tutorial :Query several EAV attributes in separate columns



Question:

I am facing issues trying to write a query. (this is slightly modified from my previous question)

My tables are laid out as follows:

 tblTicketIssues.TicketID   tblTicketIssues.RequesterID     tblPersonnelProfile.PersonnelID   tblPersonnelProfile.FirstName   tblPersonnelProfile.LastName     tblTicketAttribute.TicketID   tblTicketAttribute.Attribute   tblTicketAttribute.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

If tblTicketAttribute.Attribute= "Closed" then

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

I don't need to display all records. Only "Urgent", "Medium" & "Low".


Solution: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;  


Solution:2

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.


Solution:3

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?


Solution:4

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »