CODEWITHSUNDEEP
mysqlsqlsubquerycorrelated-subquery
Ecksters
Ecksters

Reputation: 1699

More Efficient Version of this Correlated Subquery

I've got 3 different tables and I need to pull data once from 2 of them, and twice from the third. The tables are jobs, customers, and customers_attributes. I'm trying to pull data for a specific job, and part of that data is information about the customer who owns the job. Customer data is stored in customers_attributes where the type of data is defined as an integer that corresponds with a type(using strings here for simplicity's sake) and then a content field contains the data itself.

In this case, I need to pull 2 rows from the customers_attributes table that correspond to the customer that corresponds to the job. One row for 'PhoneNumber', and another row for 'CustomerInfo'. I used an INNER JOIN for one of them, but because I can't put WHERE values for both, I used a subquery for the other one. I think this is really nasty and I'm sure there's got to be a cleaner way of doing it:

SELECT jobs.*, customers.Name AS CustomerName,
       customers_attributes.Content AS PhoneNumber,
       (     SELECT `Content`
             FROM customers_attributes
             WHERE Type = 'CustomerInfo' AND ForeignCustomer = jobs.Customer
             LIMIT 1) AS CustomerInfo
FROM jobs
INNER JOIN customers ON jobs.Customer = customers.ID
INNER JOIN customers_attributes ON jobs.Customer = customers_attributes.ForeignCustomer
WHERE jobs.ID = $jobID AND customers_attributes.Type = 'PhoneNumber'
LIMIT 1

I should mention that a customer could have multiple rows for the same attribute if they have more than 1 job, and this query ideally should either return the latest information, or the information that was submitted at the same time as the job(based on corresponding ID orders).

Upvotes: 1

Views: 128

Answers (1)

Johan
Johan

Reputation: 76641

Just join the same table again under a different alias.

SELECT j.*, c.Name AS CustomerName,
       ca.Content AS PhoneNumber,
       ca2.Content as CustomerInfo
FROM jobs j
INNER JOIN customers c ON j.Customer = c.ID
INNER JOIN customers_attributes ca ON (j.Customer = ca.ForeignCustomer)
INNER JOIN customers_attributes ca2 ON (j.Customer = ca2.ForeignCustomer)
WHERE j.ID = '$jobID' 
  AND ca.Type = 'PhoneNumber'
  AND ca2.Type = 'CustomerInfo'
LIMIT 1

Warning
It looks like you're using PHP. If you must insist on using the outdated mysql_ library and not the much improved mysqli_ lib. Please remember to use mysql_real_escape_string() and to quote your $vars.
If not you'll be hid by SQL-injection problems.

Upvotes: 1

Related Questions