Subquery in a Left Join

Question

Hi all

LEFT JOIN teams ON teams.id = (SELECT team_id FROM auth_users WHERE id = offers.user_id)

Gives me all the columns of the teams table, but converts everything to NULL.

LEFT JOIN teams ON teams.id = 1

works like a charm

When i do

SELECT (SELECT team_id FROM auth_users WHERE id = offers.user_id) as team_id

team_id will be 1.

For some strange reason it does not work inside the JOIN.

Full Query:

SELECT projects.id, projects.title as title, winner_id, projects.user_id as user_id,   until, pages, types.title as type, types.id as type_id, projects.id as id, offers.price as price, offers.delivery_date as delivery_date, teams.*,
(SELECT COUNT(id) FROM comments WHERE comments.project_id = projects.id AND comments.private = 1) as comments,
(SELECT COUNT(id) FROM uploads WHERE uploads.project_id = projects.id) as files,
(SELECT country FROM auth_users WHERE auth_users.id = offers.user_id) as baser_country,
(SELECT business FROM auth_users WHERE auth_users.id = offers.user_id) as baser_business,
(SELECT CONCAT(firstname, ' ', lastname) FROM auth_users WHERE auth_users.id = offers.user_id) as baser_name,
(SELECT team_id FROM auth_users WHERE id = offers.user_id) as team_id,
(SELECT country FROM auth_users WHERE auth_users.id = projects.user_id) as customer_country,
(SELECT business FROM auth_users WHERE auth_users.id = projects.user_id) as customer_business
FROM projects
JOIN types ON projects.type_id = types.id
LEFT JOIN offers ON projects.id = offers.project_id
LEFT JOIN teams ON teams.id = (SELECT team_id FROM auth_users WHERE id = offers.user_id)
WHERE projects.user_id = 1 AND winner_id != 0 AND uploaded = 1
GROUP BY projects.id
ORDER BY projects.id DESC
LIMIT 3

Thanks in advance!

Answer 1

First you should get rid of all these sub-queries. With the correct join, none of them is really necessary. They make the query very crowded, and they do not contribute to query performance.

To my best understanding your query should look something like this.

SELECT 
  p.id                 AS project_id, 
  MIN(p.title)         AS project_title, 
  MIN(winner_id)       AS winner_id, 
  MIN(p.user_id)       AS project_user_id,
  MIN(until)           AS until, 
  MIN(pages)           AS pages, 
  MIN(t.id)            AS type_id, 
  MIN(t.title)         AS type_title, 
  MIN(o.price)         AS offer_price, 
  MIN(o.delivery_date) AS offer_delivery_date, 
  -- m.*, <-- this should be avoided, especially in a grouped query
  COUNT(c.id)          AS count_comments,
  COUNT(u.id)          AS count_files,
  MIN(ao.country)      AS baser_country,
  MIN(ao.business)     AS baser_business,
  MIN(CONCAT(ao.firstname, ' ', ao.lastname)) AS baser_name,
  MIN(ao.team_id)      AS baser_team_id,
  MIN(ap.country)      AS customer_country,
  MIN(ap.business)     AS customer_business
FROM 
  projects p
  INNER JOIN types       t ON p.type_id    = t.id
  LEFT  JOIN offers      o ON o.project_id = p.id
  LEFT  JOIN comments    c ON c.project_id = p.id AND comments.private = 1
  LEFT  JOIN uploads     u ON u.project_id = p.id
  LEFT  JOIN auth_users ao ON ao.id = o.user_id
  LEFT  JOIN auth_users ap ON ap.id = p.user_id
  LEFT  JOIN teams       m ON m.id  = o.team_id
WHERE 
  p.user_id = 1 
  AND winner_id != 0 
  AND uploaded = 1
GROUP BY 
  p.id
ORDER BY 
  p.id DESC
LIMIT 3

Next you should never write a grouped query with fields in the output that are neither grouped nor aggregated. I've used the MIN() aggregate function on all the rogue ungrouped fields of your query.

I recommend the use of short table aliases and I recommend using the table aliases everywhere: As a third party reader I'm completely lost when it comes to the question which table "winner_id" comes from, for example.