Better SQL Query?

sql

Solution

You can make these many self-joins of table `data` more explicit, which makes the query more readable but most likely won't affect speed. I.e.:

SELECT FN.sub_id, FN.field_val AS `First Name`, 
                  LN.field_val AS `Last Name`, 
                  DOB.field_val AS `dob`, 
                  EMAIL.field_val AS `email`, 
                  PHONE.field_val AS `phone`  
FROM  $db->data FN
JOIN  $db->data LN ON (LN.field_name = 'Last Name' AND LN.sub_id = FN.sub_id)
JOIN  $db->data TEAM ON (TEAM.field_name = 'Team' AND TEAM.sub_id = FN.sub_id)
JOIN  $db->data DOB ON (DOB.field_name = 'DOB' AND DOB.sub_id = FN.sub_id)
JOIN  $db->data EMAIL ON (EMAIL.field_name = 'EMail' AND EMAIL.sub_id = FN.sub_id)
JOIN  $db->data PHONE ON (PHONE.field_name = 'Telephone' AND PHONE.sub_id = FN.sub_id)
WHERE FN.field_name = 'First Name'
ORDER BY LN.field_val

Basically, the many tedious self-join are the price you pay for this "flexible" organization of the table as a collection of attribute names and values.

BTW, if some of the data might be missing for a certain sub_id and you still want to see that row in the output (with NULL for the missing data), use LEFT JOIN instead of plain JOIN for that field's instance of the data in the above query.

Problem

The db structure: ``` fid subid fieldname fieldval ``` To get a record for a person, I do something like this: ``` $querystr = " SELECT FN.sub_id, FN.`First Name` , LN.`Last Name` , DOB.`dob` , EMAIL.`email` , PHONE.`phone` FROM ( SELECT sub_id, field_val AS 'First Name' FROM $db->data WHERE `field_name` = 'First Name' )FN, ( SELECT sub_id, field_val AS 'Last Name' FROM $db->data WHERE `field_name` = 'Last Name' )LN, ( SELECT sub_id, field_val AS `Team` FROM $db->data WHERE `field_name` = 'Team' )TEAM, ( SELECT sub_id, field_val AS `dob` FROM $db->data WHERE `field_name` = 'DOB' )DOB, ( SELECT sub_id, field_val AS `email` FROM $db->data WHERE `field_name` = 'EMail' )EMAIL, ( SELECT sub_id, field_val AS `phone` FROM $db->data WHERE `field_name` = 'Telephone' )PHONE WHERE FN.sub_id = LN.sub_id AND LN.sub_id = DOB.sub_id and DOB.sub_id = EMAIL.sub_id and EMAIL.sub_id = PHONE.sub_id ORDER BY LN.`Last Name` "; ``` Any suggestions for how to streamline this?

Original source