CODEWITHSUNDEEP
mysqlsqlentity-attribute-value
Christian Mayne
Christian Mayne

Reputation: 1749

Flattening an EAV type SQL query

I have a MySQL database used for a mailing list, and I need to extract the data, so one member record is represented by one row. The mailing list stores the user attributes as EAV. I can extract all the details I need using the following SQL query, but each member record takes up multiple rows:

SELECT a.id, a.name, a.email, b.id, b.name, b.email, c.title, d.val
FROM lists a, listmembers b, fields c, fieldsdata d
WHERE a.id = b.nl
AND b.id = d.eid
AND c.id = d.fid
ORDER BY b.id, a.id, b.name

This returns something like this:

'6', 'Mailing List name', '[email protected]', '10478', 'username', '[email protected]', 'Firstname', 'John'
'6', 'Mailing List name', '[email protected]', '10478', 'username', '[email protected]', 'Lastname', 'Smith'
'6', 'Mailing List name', '[email protected]', '10478', 'username', '[email protected]', 'Country', 'UK'
'6', 'Mailing List name', '[email protected]', '10478', 'username', '[email protected]', 'Town', 'Cambridge'
'6', 'Mailing List name', '[email protected]', '10478', 'username', '[email protected]', 'Shoesize', '7'
'6', 'Mailing List name', '[email protected]', '10478', 'username', '[email protected]', 'Favourite Colour', 'Purple'

I need to flatten this into one row using SQL, only requiring values relating to the keys firstname, lastname, town and country

The database is not huge, the fieldsdata table being the largest with about 5500 rows.

Seems like a real pain, so any pointers would be gratefully received. !

Upvotes: 2

Views: 2160

Answers (3)

theStrawMan
theStrawMan

Reputation: 235

For anyone (like me) that finds this question where your query relates to SQL Server instead of MySQL, there is the option of a Pivot query.

SELECT id, name, email, id2, name2, email2, [Firstname], [Lastname], 
    [Country], [Town]
FROM (select id, name, email, id2, name2, email2, title, val from yourresults) ps
PIVOT (
        MAX(val) 
        FOR title IN ([Firstname], [Lastname], [Country], [Town])
    ) as pvt

See SQL fiddle (working from sgeddes SQL fiddle above - thanks)

Or in MS Access:

TRANSFORM Max(val)
SELECT id, name, email, id2, name2, email2
FROM yourresults
GROUP BY id, name, .email, id2, name2, email2
PIVOT title In ("FirstName","LastName","Country","Town");

Upvotes: 3

sgeddes
sgeddes

Reputation: 62841

You can use MAX with CASE to pivot your results if I'm understanding your question correctly:

SELECT l.id, l.name, l.email, lm.id, lm.name, lm.email, 
   MAX(CASE WHEN f.title = 'Firstname' THEN fd.val END) FirstName,
   MAX(CASE WHEN f.title = 'Lastname' THEN fd.val END) Lastname,
   MAX(CASE WHEN f.title = 'Country' THEN fd.val END) Country,
   MAX(CASE WHEN f.title = 'Town' THEN fd.val END) Town
FROM lists l
   JOIN listmembers lm ON l.id=lm.nl
   JOIN fieldsdata fd ON fd.eid = lm.id
   JOIN fields f ON f.id = fd.fid 
GROUP BY l.id, lm.id

This assumes the id field from your lists table is your unique identifier. If not, you'll need to add additional fields to your GROUP BY (most likely the id field from your listmembers table).

Upvotes: 2

HLGEM
HLGEM

Reputation: 96562

EAV tables are a poor design choice and should only be used if you cannot know in advance what fields are needed. Fields such as firstname can be known that you will need them in advance and should be in proper related tables. If you want to use EAV for everything at least stop using a relational datbase and use a NOSQL database designed for this.

To get the result you want you will need to join to the EAV tables once for each type of data you want.

Upvotes: -2

Related Questions