MySQL Query from 2 Tables?

January 7, 2008 9:07 AM

How do I query data from one MySQL table, but I also need to get a single piece of data from a second table too? Should I be doing a JOIN, or a separate query?

I'm a self-taught, on-the-fly, make-it-work PHP/MySQL coder. Do little easy side projects, with basic MySQL queries, so this might be a 'duh' answer, but I don't really get it.

So I'm registering people for our class reunion. Their data gets entered into the 'users' table. They can buy tickets online through Paypal, which is linked to their users.id ID, but their Paypal purchase info is stored in the 'paypal' table. (An extra field in the paypal table lists their users.id ID.)

In the directory page that basically does a while() loop to display everyone's information with a basic SELECT * FROM 'users' query, I'd like to be able to also show which people have purchased tickets. The way I'm doing it now is having the Paypal script ALSO write to the users table to make users.tickets = 1 where id=X, which works fine, but there must be a more elegant way to do this from just one SELECT query involving both tables, right?

I tried something like SELECT users.*, paypal.purchaseduserid FROM users, paypal , but that was screwing up the loop. I basically need to display all the data from the user field, but if the paypal table also has the purchaseduserid in it that matches the current user.id, echo "blah". Thanks!

posted by gramcracker to computers & internet (9 comments total)

This is the exact situation joins were made for. I can't help you with MySQL's join syntax, but their docs should explain it.
posted by 0xFCAF at 9:10 AM on January 7

so you really want a count

SELECT users.*, count(purchaseuserid) as purchases FROM users
LEFT JOIN paypal on users.id = paypal.purchaseduserid

The left join syntax should give you all the records from users whether or not they have a purchase.
posted by bitdamaged at 9:19 AM on January 7

Your two-table query is missing a join condition, and will give you back a resultset containing every possible combination of rows from users and rows from paypal. You need to add a WHERE users.id = paypal.id to limit the rows to data that actually match.

If not every user in users has an entry in paypal, then you will need an outer join. In MySQL, that means you do SELECT users.*, paypal.purchaseduserid FROM users LEFT OUTER JOIN paypal ON users.id = paypal.id.
posted by mkb at 9:19 AM on January 7

If there is a field in the paypal table called users_id which links that paypal record to a users record, the syntax is:

SELECT users.*, paypal.purchaseuserid FROM users, paypal WHERE users.id = paypal.users_id;

To limit to people that have bought tickets, its:

SELECT users.*, paypal.purchaseuserid FROM users,paypal WHERE users.id = paypal.users_id AND users.tickets = 1;

FYI, it is customary for table names to be singular.

--FCOD
posted by flyingcowofdoom at 9:19 AM on January 7

I think this might work too

Select users.*, count(paypal.*) where paypal.purchaseusreid = users.id
posted by bitdamaged at 9:20 AM on January 7

bitdamaged left out something. That query needs a 'GROUP BY users.id' a the end.
posted by mkb at 9:20 AM on January 7

You want to do a LEFT JOIN so that all user records are returned, regardless of whether there's a matching paypal record or not. Users with no paypal record will have the paypal columns set to NULL.
posted by Khalad at 9:22 AM on January 7

SELECT * FROM users
LEFT JOIN paypal ON users.id = paypal.user_id

The resulting table will have all the columns from both users and paypal. If the user doesn't have any paypal transactions, the paypal column will be all NULLs. If a given user has two paypal transactions, there will be 2 rows for that user, with the user columns being the same, and the paypal columns showing the two different transactions.

Using a group by and a count, you could probably reduce that down, but that's over my head without the data in front of me to play with.

So the loop will look like:

for (element in sql_result) {

    print "user info"

    if (not_null paypal_column) then print "has tickets"

}

This doesn't handle a user who has two separate paypal transactions, which again can be solved via a group by. Feel free to email me with more details so I can figure out to handle that.
posted by cschneid at 9:32 AM on January 7

Man, these are all best answers. Thank you guys so much, and so quick! I thought JOIN had something to do with it, but didn't know enough about where to begin. I heart Ask Mefi.
posted by gramcracker at 9:43 AM on January 7