Alexis Alexis - 6 months ago 23
MySQL Question

MySQL - Select Query

I am some hours trying to make a query, but I haven't sucess! :(

With this query I select

users_id
and
purchase_date
of users that bought by key:

SELECT user_id, purchase_date FROM purchases AS p
INNER JOIN keys AS pc
ON p.transaction_id = pc.unique_id
WHERE pc.generator_id = 96768
GROUP BY user_id


Now I want select from these users who bought after this.
Eg.:
SELECT * FROM purchases WHERE user_id = {users of query above} AND purchase_date > {purchase_date of query above}

Answer

You can join a table with a subquery:

SELECT pr.*
 FROM purchases pr
 INNER JOIN ( 
   SELECT p.user_id, p.purchase_date
   FROM purchases p
   INNER JOIN keys pc ON p.transaction_id = pc.unique_id
   WHERE pc.generator_id = 96768
   GROUP BY p.user_id
 ) p0 ON pr.user_id = p0.user_id AND pr.purchase_date > p0.purchase_date

Info: MySQL JOIN Syntax