i have 2 tables, the first one is “users” with columns id, name, birthday_at, created_at, updated_at. The second is “orders” with columns id, user_id, created_at, updated_at.
i need “Display one random user from the shop.users table, older than 30,who made at least 3 orders in the last six months”. my request is :
"SELECT * FROM users WHERE YEAR(CURDATE()) - YEAR(birthday_at) > 30 AND (SELECT COUNT(*) FROM orders WHERE id = orders.user_id )>= 3 ORDER BY RAND() LIMIT 1;"
but it not works. can i ask you for help?
You can try this query, I included the comment to clarify
SELECT * FROM users WHERE YEAR(CURDATE()) - YEAR(birthday_at) > 30 # the first condition : older than 30 AND id IN # the second condition : more than 3 orders (SELECT orders.user_id AS id # here, we count order of user in orders table, using GROUP BY FROM orders WHERE MONTH(CURDATE()) - MONTH(created_at) <= 6 GROUP BY orders.user_id HAVING COUNT(*) >=3 # condition : >= 3 orders ) ORDER BY RAND() LIMIT 1 # get random user, you are correct