[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