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.

Answer 2

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

Answer 3

First of all - the query you want to build is not trivial, because you are trying to get some results spanned across more than one row. So I will offer you a proper solution in a fashion like it should be (read: in a way a database developer will do this :-).

First, you should modify your user_alias table so that it will contain id column but not the name. It is not good idea to join your tables using the name field. The reason for this is that there could be two Sarah Connors.

Then, you can get results from both tables using this query:

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

This way you will get your results in such format:

id | name         | address      | comments   | user
-------------------------------------------------------------
 1 | Sarah Connor | Planet Earth | Nice woman | sarah_connor
 2 | Sarah Connor | USA, NY      | Mean woman | sarah_c
 3 | John Connor  | USA, NY      | n00b       | john123

In the situations when there are two or more records in user_alias table for the same person (equal id's), you will get something like this:

id | name         | address      | comments   | user
-------------------------------------------------------------
 4 | Bill Clinton | White House  | President  | bill
 4 | Bill Clinton | White House  | President  | monica

Answer 4

Something like

SELECT user.name, user.address, user.comment FROM user
UNION ALL
SELECT user_alias.alias, user.address, user.comment 
   FROM user INNER JOIN user_alias on user.name = user_alias.name
ORDER BY name

will get you close to what you want.

You need to UNION two SELECTs together because the LEFT JOIN solution proposed by others will include only one row in the result set for users with aliases, not two as specified in your question.

But you should make the common column joining user and alias the id column, not the name column.