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

This is a silly layout for this kind of data.

To answer your question though:

Join the table to itself under different aliases.

Filter each aliased copy by a different field name, and pretend like they are different tables.

If you do this often, then it might be helpful to create a view based on this query, so that future queries don't have to be so verbose.

$querystr = "
  SELECT
    fname.sub_id
   ,fname.field_val as 'First Name'
   ,lname.field_val as 'Last Name'
   ,team.field_val as 'Team'
   ,dob.field_val as 'DOB'
   ,email.field_val as 'Email'
   ,phone.field_val as 'Phone'
  FROM  
    $db->data fname
   ,$db->data lname
   ,$db->data team
   ,$db->data dob
   ,$db->data email
   ,$db->data phone
  where fname.sub_id = lname.sub_id
    and fname.sub_id = team.sub_id
    and fname.sub_id = dob.sub_id
    and fname.sub_id = email.sub_id
    and fname.sub_id = phone.sub_id
    and fname.field_name = 'First Name'
    and lname.field_name = 'Last Name'
    and team.field_name = 'Team'
    and dob.field_name = 'DOB'
    and email.field_name = 'EMail'
    and phone.field_name = 'Telephone'  
  order by
    ln.field_val
";

Answer 2

Use an actual database schema. The approach of using a 'generic' schema that can 'hold anything' is an absolutely terrible idea. It is amazing how often this approach keeps getting 'discovered' or 'designed' over and over again by would be 'architects'.

This is often described as the EAV (entity-attribute-value) schema. All you need is four tables and then your misery begins:

* objects
* attributes
* object_attributes (objects is 1:M with object_attributes)
* links (links objects to objects, an association table)

Answer 3

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.

Answer 4

Option-1: change your db schema to have attributes in less dynamic Person table.

Option-2: Your code will work ONLY in case when you HAVE data for all the fields. Otherwise it will just not return a row to you. So not only for READABILITY, but for it to work you need to use LEFT JOINs. But I would really go for option-1.

Option-3: Why do not you just return everything from SQL to your code (SELECT * from table) and then convert it to your objects in the better suited programming language you use.

Answer 5

Just like sheepsimulator, I highly suggest that you update your data model where each field is actually it's own column. Otherwise, if your RDBMS supports it, using sub-selects like this may be faster, especially if you actually restrict the result set with a where clause at the end:

select
    s.field_val as `First Name`,
    (select field_val from $db->data as i where field_name = 'Last Name' and i.sub_id = s.sub_id) as `Last Name`,
    (select field_val from $db->data as i where field_name = 'Team' and i.sub_id = s.sub_id) as `Team`,
    (select field_val from $db->data as i where field_name = 'DOB' and i.sub_id = s.sub_id) as `DOB`,
    (select field_val from $db->data as i where field_name = 'EMail' and i.sub_id = s.sub_id) as `E-Mail`,
    (select field_val from $db->data as i where field_name = 'Telephone' and i.sub_id = s.sub_id) as `Phone`,
from $db->data as s
order by `Last Name`

Answer 6

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
INNER JOIN $db->data LN    on LN.sub_id    = FN.sub_id
INNER JOIN $db->data DOB   on DOB.sub_id   = FN.sub_id
INNER JOIN $db->data EMAIL on EMAIL.sub_id = FN.sub_id
INNER JOIN $db->data PHONE on PHONE.sub_id = FN.sub_id
WHERE FN.field_name    = 'First Name'
AND   LN.field_name    = 'Last Name'
AND   DOB.field_name   = 'DOB'
AND   EMAIL.field_name = 'EMail'
AND   PHONE.field_name = 'Telephone';

(just demonstrating Alex Martelli's good idea).

Answer 7

If it's possible, I would try to change your database schema, and make a Person table with all of those attributes (First Name, Last Name, Team, etc); it would be far more straightforward for someone who might later want to maintain this. That would make writing your query a cinch.

Of course, I'm not aware of any additional requirements you might have. I'm also assuming your'e using this on a relational database that speaks SQL, and not some other database type.