MySQL

Query multiple tables?

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2005

Posts: 1

Reputation: enh1105 is an unknown quantity at this point

Solved Threads: 0

enh1105

Offline

Newbie Poster

Query multiple tables?

Apr 17th, 2005

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?

•

Join Date: Dec 2004

Posts: 204

Reputation: pcschrottie is an unknown quantity at this point

Solved Threads: 8

pcschrottie pcschrottie is offline

Offline

Posting Whiz in Training

Re: Query multiple tables?

Apr 18th, 2005

Try this:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT p.useremail useremail, i.email email FROM Purchases p, Invites i WHERE p.useremail=i.invitee ORDER BY RAND() LIMIT 1;
SELECT p.useremail useremail, i.email email FROM Purchases p, Invites i WHERE p.useremail=i.invitee ORDER BY RAND() LIMIT 1;