...

View Full Version : Select query with two tables (field aliases etc)



greggio
03-12-2009, 03:52 PM
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.

guelphdad
03-12-2009, 04:58 PM
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.

greggio
03-12-2009, 08:48 PM
Thanks, took me a while to figure out the right syntax but got it working with two left joins.

guelphdad
03-12-2009, 09:39 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum