Query multiple tables?

[enh1105]

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 |
+------------------+----------------------+
| [e-mail] | [e-mail] |
| [e-mail] | [e-mail] |
| [e-mail] | [e-mail] |
+------------------+----------------------+

Purchases table

+------------------+----------------------+
| useremail | product |
+------------------+----------------------+
| [e-mail] | gum |
| [e-mail] | cheese |
| [e-mail] | cheese |
+------------------+----------------------+

It's a simple referral program where if [e-mail] sends an invitation to [e-mail], 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?

[pcschrottie]

Try this:

SELECT p.useremail useremail, i.email email FROM Purchases p, Invites i WHERE p.useremail=i.invitee ORDER BY RAND() LIMIT 1;

http://www.google.de/search?hl=de&q=mysql+Query+multiple+tables&btnG=Google-Suche&meta=

Michael