[RESOLVED] Get values from two rows based on values in one row when left joining two tables.

partypete

Hello all,

I have two tables, one with a list of users like so:

"users" table
user_id     user_type     user_first     user_last
22          admin         Bob            Bobson
34          author        Sally          Sallerton

And one with let's say forum posts like so:

"posts" table
post_id     post_author     post_admin
7856        34              22

What I need to do is LEFT JOIN the two tables and get the first and last names from the users table for both post_author and post_admin in the posts table and get results like:

post_id   post_author   post_admin   post_author_first   post_author_last   post_admin_first   post_admin_last
7856      34            22           Sally               Sallerton          Bob                Bobson

I'm not sure how to get values from two separate rows and place those values into new columns/fields. Would appreciate any help...

Thanks!
Peter

bradgrafelman

Sounds like you just need to JOIN the users table twice, something like:

SELECT p.post_id, p.post_author, p.post_admin, author.user_first,
    author.user_last, admin.user_first, admin.user_last
FROM posts p
LEFT JOIN users author ON p.post_author = author.user_id
LEFT JOIN users admin ON p.post_admin = admin.user_id

laserlight

You may be able to use something along these lines:

SELECT posts.*, users1.user_first AS post_author_first,
    users1.user_last AS post_author_last, users2.user_first AS post_admin_first
    users2.user_last AS post_admin_last
    FROM posts LEFT JOIN users AS users1 ON posts.post_author=users1.user_id
        LEFT JOIN users AS users2 ON posts.post_admin=users2.user_id

I note that bradgrafelman has also posted a similiar idea, but I decided to check just in case I got my syntax wrong, so you can see this as a confirmation.

bradgrafelman

That's it... JPnyc: I demand you port phpBB's new post notification over to vBulletin! :p

partypete

bradgrafelman's example seems to have done the trick! Although I must admit I don't fully understand what is happening when you write "author.user_first" etc. I really shouldn't be having trouble writing a query like this by now, but I find mysql's documentation infinitely more difficult to learn from than PHP's.

Thanks to both of you though. And after almost 13,000 posts each, the two of you are only separated by 5!!! You two in competition?? lol Whatever motivates yas, keep it up! People like me will always be here appreciating your help.

Have a good one.
Peter

bradgrafelman

partypete wrote:
I don't fully understand what is happening when you write "author.user_first" etc.

When I JOIN'd the users table, I wrote it as "LEFT JOIN users author" which is identical as writing "LEFT JOIN users AS author". In other words, I'm creating a table alias for use throughout the SQL query. Most do it for convenience, but in this case it was necessary (I believe) since you're joining the same table using two different criteria (thus "users" is ambiguous since it appears two separate times).

partypete wrote:
You two in competition?? lol Whatever motivates yas, keep it up!

Heh... we're not in any sort of competition (that I know of... o_O); I just got addicted to helping people several years ago and haven't stopped since.