SQLTeam.com | Weblogs | Forums

Help on sql query


#1

This is probably a very basic question, but much searching has produced nothing useful.

I have a table called 'users' that has 3 columns

user ID Meta_key Meta_ value
1626 last_name smith
1626 first_name fred
1626 email_address fred@hotmail.com
1627 last_name blogs
1627 first_name george
1627 email_address george@hotmail.com

etc.

And I would like the results to look like

1626 smith fred fred@hotmail.com
1627 blogs George george@hotmail.com

How do I do a sql statement that produces this?

Your help would be much appreciated !!


#2
select ln.user_id, ln.meta_value, fn.meta_value, em.meta_value
from  users ln
inner join users fn on ln.user_id = fn.user_id
inner join users em on ln.user_id = em.user_id

#3

Brilliant - thanks for the fast response, you have saved me much time ! Much appreciated !


#4

if you are able, avoid using this kind of design on your table. entity - attribute - value model.

Performance is usually bad and query is complex


#5

I totally agree on the issue of using EAV. It has it's place but must be used with caution for the reasons stated above.


#6
SELECT [user ID], 
    MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) AS last_name,
    MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) AS first_name,
    MAX(CASE WHEN meta_key = 'email_address' THEN meta_value END) AS email_address
FROM table_name
GROUP BY [user ID]
--ORDER BY last_name, first_name

#7

Unfortunately the data structure belongs to woocommerce (a retail platform) within a wordpress website - I changed the context a bit to make it simple, but many thanks all for your comments and help.

Great that you guys are able to support !


#8

I have used EAV for several years now and have had to deal with the issues related to getting data out. One of the best ways we have found that performs reasonable well is by using the PIVOT operator. Here is a link about how that works with the EAV data:

Info on Pivot for EAV Data

We use this all the time and it works well.