IA/DB

[mysql] join

kiostory 2019. 2. 27. 22:31




> select * from topic;
+----+------------+-------------------+---------------------+-----------+
| id | title      | description       | created             | author_id |
+----+------------+-------------------+---------------------+-----------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 |         1 |
|  2 | Oracle     | Oracle is ....    | 2018-01-03 13:01:10 |         2 |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 |         2 |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 |         3 |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 |         4 |
+----+------------+-------------------+---------------------+-----------+
5 rows in set (0.0055 sec)




> select * from author;
+----+---------+---------------------------+
| id | name    | profile                   |
+----+---------+---------------------------+
|  1 | egoing  | developer                 |
|  2 | kio     | database administrator    |
|  3 | freebee | data scientist, developer |
|  4 | cookieg | CIO of cookio             |
+----+---------+---------------------------+





> select * from topic left join author on topic.author_id=author.id;
+----+------------+-------------------+---------------------+-----------+----+---------+---------------------------+
| id | title      | description       | created             | author_id | id | name    | profile                   |
+----+------------+-------------------+---------------------+-----------+----+---------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 |         1 |  1 | egoing  | developer                 |
|  2 | Oracle     | Oracle is ....    | 2018-01-03 13:01:10 |         2 |  2 | kio     | database administrator    |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 |         2 |  2 | kio     | database administrator    |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 |         3 |  3 | freebee | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 |         4 |  4 | cookieg | CIO of cookio             |
+----+------------+-------------------+---------------------+-----------+----+---------+---------------------------+


> select id,title,description,created,name,profile from topic left join author on topic.author_id=author.id;
ERROR: 1052 (23000): Column 'id' in field list is ambiguous



> select topic.id,title,description,created,name,profile from topic left join author on topic.author_id=author.id;
+----+------------+-------------------+---------------------+---------+---------------------------+
| id | title      | description       | created             | name    | profile                   |
+----+------------+-------------------+---------------------+---------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 | egoing  | developer                 |
|  2 | Oracle     | Oracle is ....    | 2018-01-03 13:01:10 | kio     | database administrator    |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | kio     | database administrator    |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | freebee | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 | cookieg | CIO of cookio             |
+----+------------+-------------------+---------------------+---------+---------------------------+