Using MySQL I need to return new first/last name columns for each user in table1.
**Table1** uid 1 2 **Table2** id fid uid field_value 1 1 1 Joe 2 2 1 Blow 3 1 2 Joe 4 2 2 Blogs **Result** uid first_name last_name 1 Joe Blow 2 Joe Blogs
-
Be careful using this approach. The more fields you want to extract, the more joins you need. Big joins are costy in MySQL. Joining just 3 tables with 30K rows in each, can take up to 1-5 minutes, despite having all the indices and being cached.culebrón– culebrón2009-09-17 09:03:13 +00:00Commented Sep 17, 2009 at 9:03
Add a comment
|
3 Answers
The solution is simple,
select t1.uid, t21.field_value first_name, t22.field_value last_name
from table1 t1, table2 t21, table2 t22
where t1.uid=t21.uid and t1.uid=t22.uid and t21.fid=1 and t22.fid=2
Comments
This should do it (not tested):
select a.uid, a.field_value first_name, b.field_value last_name
from table2 a inner join table2 b on a.uid = b.uid
where a.fid = 1 and b.fid = 2
Comments
assuming you have columns first_name, and last_name in table2:
select uid, first_name, last_name from table1 left join table2 on table1.uid=table2.uid
