How to join mysql tables

Question

I've an old table like this:

user> id | name | address | comments

And now I've to create an "alias" table to allow some users to have an alias name for some reasons. I've created a new table 'user_alias' like this:

user_alias> name | user

But now I have a problem due my poor SQL level... How to join both tables to generate something like this:

1 | my_name    | my_address    | my_comments
1 | my_alias   | my_address    | my_comments
2 | other_name | other_address | other_comments

I mean, I want to make a "SELECT..." query that returns in the same format as the "user" table ALL users and ALL alias.. Something like this:

SELECT user.* FROM user LEFT JOIN user_alias ON `user`=`id`

but it doesn't work for me..

Answer 1

I think you need something like this:

SELECT user.*
FROM user
LEFT JOIN user_alias
ON user.name=user_alias.name

Your original query was not specific enough in the join condition.