Re: How to query this table

[Chris Stubben]

Hi,

Both queries listed by Mike will work, but I think the self-join will be faster even for large tables (since you have an index on UserId).

You can also use crosstab techniques to flatten out this table. Since every userid and fieldname combination are unique, you can use max() with a conditional to just return the fielddata value. The group_concat() function would also work.

select userId,
max( if(fieldName='firstname', fieldData, null)) as first,
max( if(fieldName='lastname', fieldData, null)) as last,
max( if(fieldName='email', fieldData, null)) as email,
max( if(fieldName='emailSubscribe', fieldData, null)) as subscribe
from userProfileData
group by 1;

+--------+-------+--------+-----------------------------+-----------+
| userId | first | last | email | subscribe |
+--------+-------+--------+-----------------------------+-----------+
| 3 | NULL | Smith | [e-mail] | yes |
| 4 | Doug | Brown | [e-mail] | no |
| 5 | Jane | Thomas | [e-mail] | yes |
| 6 | John | Black | [e-mail] | no |
+--------+-------+--------+-----------------------------+-----------+


You can copy this into a new table or just add a having clause to get rows where subscribe='yes' (this will probably be slower than the join, but probably faster than a sub-query)


select userId,
max( if(fieldName='firstname', fieldData, null)) as first,
max( if(fieldName='lastname', fieldData, null)) as last,
max( if(fieldName='email', fieldData, null)) as email,
max( if(fieldName='emailSubscribe', fieldData, null)) as subscribe
from userProfileData
group by 1
having subscribe='yes';

+--------+-------+--------+----------------------------+-----------+
| userId | first | last | email | subscribe |
+--------+-------+--------+----------------------------+-----------+
| 3 | NULL | Smith | [e-mail] | yes |
| 5 | Jane | Thomas | [e-mail] | yes |
+--------+-------+--------+----------------------------+-----------+



Chris