Better SQL Query?

Question

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?

Answer 1

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.