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]

Originally Posted by selwonk

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:

[SQL]INNER JOIN user userwho ON userwho.userid = reputation.whoadded[/SQL]

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]

Originally Posted by Paul M

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!