phpmyadmin Question posted in
The official documentation can be found here.

How to make this Query without using UNION? – Phpmyadmin

I am not very good at making query’s. So could someone explain and help me with this query?

I need to inner join 2 tables ‘Slaap’ And ‘Eten’. i need all ‘eten’ where ID = 5 and i need all ‘Slaap’ where ID = 5. I could only achieve this with a UNION but that is what i dont want because this query is for making the latest activity so i can use ORDER BY but i can’t because of the UNION.

This is my current Query:

SELECT e.tijd, e.product_1, e.product_2, e.product_3, s.van, s.tot, s.slaapoptie 
FROM users u 
    LEFT OUTER JOIN eten e on e.user_id = u.id AND e.id = 5 
    LEFT OUTER JOIN slaap s on s.user_id = u.id WHERE u.id = 5 
UNION 
SELECT e.tijd, e.product_1, e.product_2, e.product_3, s.van, s.tot, s.slaapoptie 
FROM users u 
    LEFT OUTER JOIN eten e on e.user_id = u.id 
    LEFT OUTER JOIN slaap s on s.user_id = u.id AND s.id = 5 
    WHERE u.id = 5

With as result

query

I want the same result but without using UNION so someone explain this for me and how can i achieve this?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT * FROM( SELECT e.created_eten, e.tijd, e.product_1, e.product_2, e.product_3, s.created_slaap, s.van, s.tot, s.slaapoptie FROM users u LEFT OUTER JOIN eten e on e.user_id = u.id AND e.id = ? LEFT OUTER JOIN slaap s on s.user_id = u.id WHERE u.id = ? UNION SELECT e.created_eten, e.tijd, e.product_1, e.product_2, e.product_3, s.created_slaap, s.van, s.tot, s.slaapoptie FROM users u LEFT OUTER JOIN eten e on e.user_id = u.id LEFT OUTER JOIN slaap s on s.user_id = u.id AND s.id = ? WHERE u.id = ?

    this worked for me


  2. 0

    I think this does what you want:

    SELECT e.tijd, e.product_1, e.product_2, e.product_3, s.van, s.tot, s.slaapoptie 
FROM users u LEFT OUTER JOIN
     eten e 
     ON e.user_id = u.id LEFT JOIN
     slaap s 
     ON s.user_id = u.id AND s.id = u.id
WHERE u.id = 5 AND
      (e.id IS NOT NULL OR s.id IS NOT NULL);

    Note that I replaced the 5 with u.id. I am guessing that is the intention.

    The final condition in the WHERE is just checking that at least one condition matches.

    Login or Signup to reply.
  3. 0

    seems you want users that have at eten or slaap..
    so you can do

    SELECT e.tijd, e.product_1, e.product_2, e.product_3, s.van, s.tot, s.slaapoptie 
FROM users u 
LEFT OUTER JOIN eten e on e.user_id = u.id 
LEFT OUTER JOIN slaap s on s.user_id = u.id 
WHERE u.id = 5
and (e.id = 5 or s.id = 5)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search