mysql - More Efficient Version of this Correlated Subquery -

mysql - More Efficient Version of this Correlated Subquery -

i've got 3 different tables , need pull info 1 time 2 of them, , twice third. tables jobs, customers, , customers_attributes. i'm trying pull info specific job, , part of info information client owns job. client info stored in customers_attributes type of info defined integer corresponds type(using strings here simplicity's sake) , content field contains info itself.

in case, need pull 2 rows customers_attributes table correspond client corresponds job. 1 row 'phonenumber', , row 'customerinfo'. used inner bring together 1 of them, because can't set values both, used subquery other one. think nasty , i'm sure there's got cleaner way of doing it:

select jobs.*, customers.name customername, customers_attributes.content phonenumber, ( select `content` customers_attributes type = 'customerinfo' , foreigncustomer = jobs.customer limit 1) customerinfo jobs inner bring together customers on jobs.customer = customers.id inner bring together customers_attributes on jobs.customer = customers_attributes.foreigncustomer jobs.id = $jobid , customers_attributes.type = 'phonenumber' limit 1

i should mention client have multiple rows same attribute if have more 1 job, , query ideally should either homecoming latest information, or info submitted @ same time job(based on corresponding id orders).

just bring together same table 1 time again under different alias.

select j.*, c.name customername, ca.content phonenumber, ca2.content customerinfo jobs j inner bring together customers c on j.customer = c.id inner bring together customers_attributes ca on (j.customer = ca.foreigncustomer) inner bring together customers_attributes ca2 on (j.customer = ca2.foreigncustomer) j.id = '$jobid' , ca.type = 'phonenumber' , ca2.type = 'customerinfo' limit 1

warning looks you're using php. if must insist on using outdated mysql_ library , not much improved mysqli_ lib. please remember utilize mysql_real_escape_string() , quote $vars. if not you'll hid sql-injection problems.

mysql sql subquery correlated-subquery