MYSQL: Handling Multiple LEFT JOINS

Question

I have a query with one LEFT JOIN that works fine. When I add a second LEFT JOIN to a table with multiple records per field in the first table, however, I am getting the product of the results in the two tables ie books x publishers returned. How can I prevent this from happening?

 SELECT a.*,b.*,p.*, group_concat(b.id as `bids`) 
    FROM authors `a`
    LEFT JOIN books `b`
    ON b.authorid = a.id 
    LEFT JOIN publishers `p` 
    on p.authorid = a.id 
    GROUP by a.id

EDIT:

Figured it out. The way to do this is to use subqueries as in this answer:

SELECT u.id
     , u.account_balance
     , g.grocery_visits
     , f.fishmarket_visits
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS grocery_visits
   FROM   grocery
   GROUP  BY user_id
   ) g ON g.user_id = u.id
LEFT   JOIN (
   SELECT user_id, count(*) AS fishmarket_visits
   FROM   fishmarket
   GROUP  BY user_id
   ) f ON f.user_id = u.id
ORDER  BY u.id;

update your question add a proper data sample and the expected result — ScaisEdge, Jul 02 '17 at 19:19
your question is not clear ... please add a proper data sample and the expected result .. — ScaisEdge, Jul 03 '17 at 05:29

score 0 · Answer 1 · answered Jul 03 '17 at 18:22

If you do multiple LEFT Joins, your query will return a cartesian product of the results. To avoid this and get only one copy of fields you desire, do a subquery for each table you wish to join as below. Hope this helps someone in the future.

SELECT u.id
     , u.account_balance
     , g.grocery_visits
     , f.fishmarket_visits
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS grocery_visits
   FROM   grocery
   GROUP  BY user_id
   ) g ON g.user_id = u.id
LEFT   JOIN (
   SELECT user_id, count(*) AS fishmarket_visits
   FROM   fishmarket
   GROUP  BY user_id
   ) f ON f.user_id = u.id
ORDER  BY u.id;

MYSQL: Handling Multiple LEFT JOINS

1 Answers1