Fabian Tschullik Fabian Tschullik -4 years ago 105
MySQL Question

SQL select with with multiple rows for one ID

I have two tables:

Table 1: 'user_fields'

id name
1 age
2 birthdate
3 firstname

Table 2: 'user_data'

user_id user_field_id value
22 1 50
22 2 01.01.1990
22 3 Peter
25 1 33
25 2 05.08.1970
25 3 Steve

Now I would like to create a sql statement which shows firstname and birthday for each user_id

example rows:

22 Peter 01.01.1990
25 Steve 05.08.1970

How should the select look like?
Thanks a lot!

Answer Source

You'll need to join the tables (if you don't want to hardcode the values), and then perform an aggregation:

SELECT  ud.user_id,
        MIN(CASE WHEN uf.name = 'firstname' THEN ud.value END) as firstname,
        MIN(CASE WHEN uf.name = 'birthday' THEN ud.value END) as birthday
FROM user_data ud
INNER JOIN user_fields uf
    ON ud.user_field_id = uf.user_field_id 
GROUP BY ud.user_id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download