How to connect to mysql tables

Question

I have an old table:

user> id | name | address | comments 

And now I have to create the "alias" table so that some users can have an alias for some reason. I created a new user_alias table as follows:

 user_alias> name | user 

But now I have a problem due to my low level of SQL ... 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 want to say that I want to make a "SELECT ..." query that returns in the same format as the "user" table ALL users and ALL alias. Something like that:

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

but it does not work for me.

Answer 1

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 

brings you closer to what you want.

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

But you have to make a common column connecting the user and an alias of the id column, not the name column.

Answer 2

I think you need something like this:

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

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

Answer 3

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

Answer 4

First of all, the query you want to build is not trivial because you are trying to get some results related to multiple rows. Therefore, I offer you the right solution in the form it should be (read: as the database developer will do this :-).

You must first modify the user_alias table so that it contains the id column, but not the name. It is not recommended to join your tables using the name field. The reason for this is that there may be two Sarah Connors.

Then you can get the results from both tables with this query:

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

Thus, you will get the results in this 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 situations where there are two or more entries (equal to id ) in the user_alias table for the same person, 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