Table Join help needed

[paul_watkins]

Hi

I'm having some problems working out the best way to perform a join on 2 tables when i need to match 2 records from 1 of the tables.

Basically, i have a user table and a publish table.

The user table contains all the users on the site (userID and userName)
The publish table contains a number of records that show when content was submitted and approved. (submittedBy (userID), approvedBy (userID), contentID).

Basically i need to query the publish table and get the names of the 2 users that submitted and approved the content.

So the result would be
contentID, submittedBy Ted, approvedBy Bob.

I've tried a few JOIN statements and EXISTS but just can't get this to work.

Can anyone help? Or suggest another way of storing the data i need?

Thanks for your help

[MGenev]

What SQL server are you using?
Won't something like

select p.contentid, u1.username, u2.username
from publish as p
join users as u1 on p.submittedby = u1.id
join users as u2 on p.approvedby = u2.id

be appropriate?