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?