Selection from two tables

Question

MySQL

There are two tables with the following content:

mysql> select * from users;
+----+-------+--------+
| id | name  | phone  |
+----+-------+--------+
|  1 | name1 | phone1 |
|  2 | name2 | phone2 |
+----+-------+--------+

and

mysql> select * from devices;
+---------+---------+-------------+
| user_id | device  | address     |
+---------+---------+-------------+
|       1 | router1 | 192.168.0.1 |
|       1 | router2 | 192.168.1.1 |
|       2 | router1 | 192.168.3.1 |
+---------+---------+-------------+

Need a selection from two tables

SELECT id, phone, device, address FROM users,devices...

to get a string like:

1,phone1,router1,192.168.0.1,router2,192.168.1.1

Answer 1

SELECT a.id,a.phone,b.device,b.address FROM users as a,devices as b WHERE a.id = b.user_id;

Answer 2

JOIN help
www.mysql.ru/docs/man/JOIN.html

Answer 3

In addition to the above:
Aldorr threw off a link to the description of JOINs, but to make it easier to understand what it is and what it is eaten with, I advise you to look at: www.codinghorror.com/blog/2007/10/a-visual-explanation -of-sql-joins.html
Akint described a working query, but it will not display users without "devices", for such outputs, it is better to use:

SELECT a.id,a.phone,b.device,b.address FROM users AS a LEFT JOIN devices AS b ON a.id = b.user_id;

Answer 4

1. Doing this in a query is not very correct, because the result may simply not fit into the DBMS limitation on the size of the string in the cell. Therefore, it is better to solve the problem in a specific language in a loop and collect lines there, for example, in PHP.
2. Hierarchical queries are poorly supported by MySQL.
True, there are some attempts, you can start reading from here explainextended.com/2009/03/20/hierarchical-queries-in-mysql-finding-leaves/