0

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?

2
Contributors
1
Reply
2
Views
12 Years
Discussion Span
Last Post by pcschrottie
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.