Best SQL query?

Question

Db structure:

fid  
subid  
fieldname  
fieldval 

To get a record for a person, I am doing 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 optimizing this?

Answer 1

You can make these multiple table self-joins datamore explicit, which makes the query more readable, but most likely will not affect the 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, many tedious self-joining is the price you pay for this “flexible” table organization as a collection of attribute names and values.

BTW, sub_id, ( NULL ), LEFT JOIN JOIN .

Answer 2

, Person (, , ..); , . cinch.

, , . , , SQL, - .

Answer 3

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';

( ).

Answer 4

. " ", " -", . , "" "" , "".

EAV (--). , , , :

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

Answer 5

sheepsimulator, , . , RDBMS , , , , where :

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

-1: db Person.

-2: , . . , READABILITY, LEFT JOIN. -1.

-3: SQL (SELECT * ), , .

Answer 7

.

:

.

, .

, , , .

$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
";