SQL query query

dahamsta

Never been very good with advanced SQL stuff.

Say I have a 'users' table which contains two fields, 'userid' and 'username'; and a 'news' table which contains several news fields together with the id of the user that submitted the story and the id of the moderator that approved the story. Is there anyway to get the /usernames/ of both the user and the mod in each row while querying data from the news table? Something like...

SELECT news.*,users.username,users.username FROM news,users;

...where the first 'users.username' above will return the username of the submitting user, and the second will return the username of the moderator. I guess I'm looking for something like AS, but that's onle for tables, right? This making sense to /anyone/?

adam

sixpack's little hat

sounds like a self-join if i'm reading Q correctly


could use something like this:

select user1.username,user2.username
from users user1, users user2,news n
where user1.username = n.userid
and user2.username=n.modid;

bonkey

Its not a self-join, but the SQL sixpack quoted should work.

If you prefer ANSI syntax, it would go something like

SELECT news.*,
sub.username,
mod.username

FROM news
INNER JOIN users AS sub
ON news.submittedby = sub.userid
INNER JOIN users as mod
ON news.approvedby = mod.userid

And then stick a WHERE on the end of all that if you want to filter for anything.

jc

dahamsta

Thanks guys. It took me a couple of hours to figure out that although the queries looked right, they weren't working because the userid I used in the moderator field didn't exist in the users table. Duh.

Thanks again.
adam