Given:
t1, t2 - tables of orders and users, respectively
in t1 fields id, user, date
in t2 fields id and email

Task:
Write a SQL query for a sample of t1.id and t2.email of those users whose orders were made earlier than 3 days ago.

Dug out here that:

SELECT user FROM t1 WHERE date < (NOW() - INTERVAL 3 DAY)

It remains to make a selection from t2 (WHERE id = t1.user)
I can do this in php, but I need it in one request.

  • 2
    Read on join. - Chad
  • @ Chad, thank you, figured it out. RIGHT OUTER JOIN solves the problem. - lommusic

1 answer 1

You can extract users who ordered earlier than three days ago.

 SELECT users.email AS email FROM t2 AS users LEFT JOIN t1 AS orders ON users.id = orders.user WHERE orders.date < (NOW() - INTERVAL 3 DAY) AND orders.id IS NOT NULL

However, this query does not retrieve orders, if you try to add orders.id to the SELECT clause, then any random order identifier will be extracted to MySQL (if the user has several).

If you use just a JOIN union, you can retrieve all orders placed earlier than three days. However, users users.email will be repeated, the number of times the user placed an order.

 SELECT orders.id AS order_id, users.email AS email FROM t2 AS users JOIN t1 AS orders ON users.id = orders.user WHERE orders.date < (NOW() - INTERVAL 3 DAY)

Alternatively, you can use the first LEFT JOIN query, retrieving the list of order IDs using the GROUP_CONCAT () function

 SELECT users.email AS email, GROUP_CONCAT(orders.id) AS orders_ids FROM t2 AS users LEFT JOIN t1 AS orders ON users.id = orders.user WHERE orders.date < (NOW() - INTERVAL 3 DAY) AND orders.id IS NOT NULL