[thelist] MySQL 5 Query

r937 rudy at r937.com
Thu Jul 3 06:49:05 CDT 2008


> select mail, x.value as value1 , y.value as value2 from
> (select users.mail, profile_values.value
> from users
> left join profile_values on users.uid=profile_values.uid
> where users.uid=1 and fid=1) as x ,
> (select   profile_values.value
> from users
> left join profile_values on users.uid=profile_values.uid
> where users.uid=1 and fid=2) as y
>
> Is this the correct and/or intelligent to go about this? Or is there a 
> better way to do this in MySQL 5?

INNER JOINs would be more appropriate

you're using a LEFT OUTER JOIN with a WHERE condition on one of the columns 
of the right table

in effect, this means that you will never return a users row with an 
unmatched profile_values row, because if it were unmatched, then fid would 
be NULL, and then the WHERE condition (fid=1, fid=2) would eliminate that 
row

as for your query, it's fine, although if you need to return other data from 
the profile_values table, i suspect those queries will get messy rather 
quickly

see

  Dave's guide to the EAV
  http://weblogs.sqlteam.com/davidm/articles/12117.aspx

  OTLT and EAV: the two big design mistakes all beginners make
  http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html


rudy 





More information about the thelist mailing list