select users and the primary user

[Gman]

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)

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

[r937]

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

[Gman]

Thanks Rudy! I really need to buy your book