Select query with two tables (field aliases etc)

Hey guys, I've been trying to figure out how to do this for a while (quite a while...) with a single query.

Let's say I have two tables with at least the following fields:

*Table users: (users who can edit/add records)

- user_id
- user_name

*Table contact_notes: (notes for various contacts which are stored in another table)

- note_id
- contact_id
- note_content
- note_created_by (id)
- note_modified_by (id)

Now I need a query that will list all the notes for a certain contact_id and I want the names of the users that created and modified a note (can be different users of course).

Here's an example:

'users' table:

user_id user_name
1 Ben
2 Bob
3 Bill


'contact_notes' table:

note_id contact_id note_content note_created_by note_modified_by
1 8 sometext1 1 3
2 8 sometext2 1 2
3 8 sometext3 2 3


Now I can get the name of the user who created a note easily with a simple join:

SELECT *, users.user_name AS created_by FROM users
INNER JOIN contact_notes c ON (users.user_id = c.note_created_by)
WHERE c.contact_id = 8

This would return the name of the user who created the note in 'created_by' field.

How do I modify the query so that I get the name of who modified the note as well? I'd like the result set to look something like this:


note_id note_content created_by modified_by
1 sometext1 Ben Bill
2 sometext2 Ben Bob
3 sometext3 Bob Bill


Well, I hope I was clear enough. Thanks in advance for any help.

Get out of the bad habit of using SELECT *, name the columns you actually need.

You will need two joins on the users table once to retrieve the one name and a second (with a different alias for the table) to retrieve the second name.

Thanks, took me a while to figure out the right syntax but got it working with two left joins.

you don't need left joins unless you want a list of people who haven't created or modified items.

you can use an INNER JOIN for that. if you do want them listed then the LEFT OUTER is correct.