INNER JOIN query

[selwonk]

Hi folks

Apologies in advance if this has been covered - I couldn't find the answer having done a bit of searching...

I run vBadvanced and I want to produce a thank you box on the front page which shows the latest 10 positive reputation comments. The idea is to promote the reputation system and give people added incentive to give and receive rep points

The information I want is in the reputation and user table. I want to get the following fields:

reputation.postid
reputation.userid
reputation.whoadded
reputation.reason
user.username

I've got this working with the following query:SELECT
reputation.postid,
reputation.userid,
reputation.whoadded,
reputation.reason,
user.username
FROM
reputation
INNER JOIN
user
ON
reputation.userid=user.userid
WHERE
reputation.reputation >= 1 AND
reputation.reason <> ''
ORDER BY
reputation.dateline DESC
LIMIT 10;However, I would also like to map reputation.whoadded to user.username as well. Is it possible to do two INNER JOINs to get this information?

I'd be very grateful of any advice. I'm not feeling well today, and my brain hurts :)

Thanks in advance...

[Farcaster]

However, I would also like to map reputation.whoadded to user.username as well. Is it possible to do two INNER JOINs to get this information?

I'd be very grateful of any advice. I'm not feeling well today, and my brain hurts :)

Thanks in advance...

Yes, it is possible, you just need to use a table alias. So your join would look like this example:

INNER JOIN user userwho ON userwho.userid = reputation.whoadded

And in your select, you'd reference it by userwho.username

[Paul M]

It's good practice to use 'AS' (INNER JOIN user AS userwho .....). :)

[Farcaster]

It's good practice to use 'AS' (INNER JOIN user AS userwho .....). :)

It may make it slightly more readable if that is the format you are used to using, but I find it to be superfluous and I usually omit it.

[selwonk]

Ok folks - thanks for that. I'll give it a go and report back

Thank you

--------------- Added at 12:12 ---------------

Thanks chaps - that gave me what I needed. The resultant query looks like this:SELECT
reputation.postid,
reputation.userid,
reputation.whoadded,
reputation.reason,
user.username,
userwho.username AS usernamefrom

FROM
reputation

INNER JOIN
(user INNER JOIN user AS userwho ON reputation.userid=user.userid) ON reputation.whoadded=userwho.userid

WHERE
reputation.reputation >= 1 AND
reputation.reason <> ''

ORDER BY
reputation.dateline DESC
LIMIT 10;Thanks again!