Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:
    Code:
    user_id   user_name
    1           Ben
    2           Bob
    3           Bill
    'contact_notes' table:
    Code:
    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:

    Code:
    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.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, took me a while to figure out the right syntax but got it working with two left joins.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •