I'm having trouble figuring out how to query multiple tables.
(Note: I've greatly simplified the tables for clarity purposes)
I have 2 tables: Invites and Purchases
The invites table has the following fields: email & invitee
The purchases table has the following fields: useremail & product
Invites tables
+------------------+----------------------+
| email | invitee |
+------------------+----------------------+
| joe@joe.com | sam@sam.com |
| joe@joe.com | max@max.com |
| joe@joe.com | ken@ken.com |
+------------------+----------------------+
Purchases table
+------------------+----------------------+
| useremail | product |
+------------------+----------------------+
| joe@joe.com | gum |
| sam@sam.com | cheese |
| ken@ken.com | cheese |
+------------------+----------------------+
It's a simple referral program where if joe@joe.com sends an invitation to sam@sam.com, joe gets credit if sam buys something.
But, what I want to do is this:
I want to find all the useremail from the purchases table, and all the useremails in the purchases table that stem from the invites sent, and then display one of these
emails randomly.
Basically, I want to find a way to combine the following statements:
1) SELECT useremail FROM purchases;
2) SELECT email FROM invites WHERE purchases.useremail=invites.invitee
3) ORDER BY RAND() LIMIT 1;
I've looked at JOIN and UNION, but neither are making much sense to me, and everything I try generates an error message.
Is this possible? Can anyone help out?
