Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – inner join on two tables simultaneously

I have this join:

SELECT * FROM persons p
LEFT JOIN orders o
    ON p.id = o.person_id
LEFT JOIN old_orders oo
    ON p.id = oo.person_id LIMIT 100

orders and old_orders have exactly the same structure and for misc reasons they can’t be merged into one table.

Right now, any person with or without orders will be returned, I would like to change those joins into INNER JOIN so the returned persons are only the ones with order history. The problem is 1st inner join will filter out any persons that might have been present in the next one.

I tried to INNER JOIN a UNION of those two tables but the performance hit is just too big since it fetches all rows.

Tags:

2

Answers


  1. 0

    Try this:

    SELECT * FROM persons p
LEFT JOIN orders o
ON p.id = o.person_id
LEFT JOIN old_orders oo
ON p.id = oo.person_id 
WHERE (o.id is not null) or (oo.id is not null) 
LIMIT 100
    Login or Signup to reply.
  2. 0

    Even solving your stated problem, if a p has 3 o rows and 4 oo rows, you are going to end up with 12 rows for that p, where a union would properly get you 7.

    The key here is understanding how you want your results ordered before applying the limit; if you truly don’t care (as your lack of an order by indicates), do your inner join of a union but apply a limit 100 to both unioned queries (requires parentheses around the second select to keep the limit from only applying to the union as a whole).

    If you do care about what order, or are trying to get 100 distinct people, or something else, you need to provide more information.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search