I have two tables like this:
users:
+----------+---------------+
| id | email |
+----------+---------------+
| 1 | a@example.com |
| 2 | b@example.com |
| 3 | c@example.com |
| 4 | d@example.com |
+----------+---------------+
transactions:
+----+------+----------+
| id | user | type |
+----+------+----------+
| 1 | 1 | purchase |
| 2 | 1 | sale |
| 3 | 1 | sale |
| 4 | 2 | other |
| 5 | 2 | purchase |
| 6 | 3 | purchase |
| 7 | 3 | sale |
| 8 | 3 | other |
+----+------+----------+
I need to return only the email addresses of users who only have "purchase" and "sale" entries in the transactions table. If they have any other transaction type, I dont want them returned (i.e. users ID 2 and 3 in the example). But they must have at least one "purchase" or "sale" transaction. So in the example, I'd just want to return a@example.com because they have at least one "purchase" or "sale" entry in transactions (but no other type).
I've looked at a similar question mysql select result based on multiple matching criteria of 2 or more columns but in my case there could be multiple entries in the transaction table, for "sale" and/or "purchase" so counting the results doesn't help.
Is this possible and could you help me with the query?
