Selecting a list from a separate table

Question

MySQL

There are two tables. How to extract data from the first table and a list from name from the second table in one query? Those. the result should be "1", "my title", "name1, name3". I remember exactly there was an article on Habré, I have already rummaged through everything, I can’t find it.
id | title
---------
1 | my title
2 | other title

id | sourceid | name
-------------------------
1 | 1 | name1
2 | 2 | name2
3 | 1 | name3



Answer 1

SELECT table1.`id`, table1.`title`, GROUP_CONCAT(table2.`name` SEPARATOR ', ') as names 
FROM `table1` JOIN table2 ON table1.`id` = table2.`sourceid` 
GROUP BY table2.`sourceid`

Answer 2

Something like this:
SELECT t1.*, GROUP_CONCAT(t2.name) FROM t1 JOIN t2 ON(t1.id = t2.sourceid) WHERE t1.id = 1 GROUP BY t2.sourceid
Didn't check :)

Answer 3

I warn you right away , I am not a database guru and in general it is better to ask such questions on specialized resources, for example, on sql.ru.
There are nested queries, google it. But, in my opinion, this will not give a special increase, they are needed simply in order to transfer the logic from the application to the request. That is, in terms of performance, it is equivalent to loading data from the first table, and then looping from the second.
In general, if performance were very important, I would do this:
1) if the expected data array is small, then I would select it with one request
2) if it is large, then two, the second in the id list, since it can eat a lot of memory
I would process the received data and would lead to the required form already directly in the application.

Answer 4

Googled this: webi.ru/webi_articles/8_14_f.html
It looks like this is what you were looking for. But, it seems to me, this is a crutch, it is unlikely that it will always or at least sometimes work faster.

Answer 5

GROUP_CONCAT basically does the job. But there was a theme here and there was something special. Thanks everyone for the replies.