SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select users and the primary user

    Hey all,

    So lets say I have 2 tables:
    companies
    [id]
    [name]
    [primary_user] = users.id

    users
    [id]
    [first_name]
    [last_name]

    I need to show all users AND show the primary user, right now I am only showing the primary user
    (for use in a php select field)
    Code:
    SELECT 
             u.id
             , u.first_name
             , u.last_name
    FROM
           users AS u
    LEFT JOIN
            companies AS c ON
            u.id = c.primary_user
    WHERE
             c.id = 1
    ORDER BY 
             u.first_name
    On a side note, is it the norm to store a persons name in two fields or one field? I know I can explode it on a white space to separate the names, but what if someone has a funky name like: Bob Jones Smith Willams lol

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    change the word WHERE to AND, so that it adds the c.id=1 condition to the ON clause of the join

    you'll prolly also want to add one or more columns from the c table to your SELECT clause

    as for name column(s), that's your call, lol... but you did pick up on one of the issues
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy! I really need to buy your book


Bookmarks

Posting Permissions

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