How to make a sql query to select articles and comments from related tables?

Question

MySQL

Hello.
There is such structure of MySQL database:
Table of articles (story)
- id
- text
- author_id
...
Table of comments (story_comments)
- id
- s_id (id of article)
- text
...
Tables are connected
I need to select articles and along the way comments to them.
I got something like this:

SELECT 
story.id s_id, 
story.author_id s_author_id, 
story.text s_text,
 ...
story_comments.id comment_id, 
story_comments.text comment_text
 ... 
FROM (SELECT * FROM story WHERE story.id IN ($ids)) story 
LEFT JOIN story_comments ON story.id = story_comments.s_id

The first time I wrote this:

SELECT 
story.id s_id,
story.author_id s_author_id, 
story.text s_text,
 ...
story_comments.id comment_id, 
story_comments.text comment_text
 ... 
FROM story 
LEFT JOIN story_comments ON story.id = story_comments.s_id 
WHERE story.id IN ($ids)

There are two questions:
1. Which of the top queries loads the database faster and less
2. Does everyone do this or am I choosing the comments incorrectly? An array of results is returned to me like: [data of the first article, first comment], [data of the first article, second comment], ...
Can I somehow make it return: [data of the first article, [first comment, second, .. .]],[second article,[...]]

Answer 1

Everything should be on one line,
in the comments the separator is '||'.
Didn't check.

select 
s.id,
s.text,
ifnull(c.comments, "нет комментариев")
from story s
left join 
(
  select 
  s_id,
  group_concat(text SEPARATOR '||') as comments
  from story_comments
  group by s_id
) c on c.s_id = s.id

Answer 2

In this case, it is more correct to choose the article itself as a separate request, and comments to it as a separate request. Otherwise, the article (and this is usually several kilobytes of text) will be transmitted along with each comment, dramatically increasing traffic.

Answer 3

answer to 1 question:
In theory, the second option is faster (the one that you have marked as - "The first time I wrote this"
because:
- SELECT * FROM
should not be written like that - Asterisk always works slower than listing columns, not by much :) but yet
- just a Left join will work faster than a subquery - especially if there are necessary indexes
answer to question 2:
"to return: [data of the first article, [first comment, second, ...]]" (c)
support for such a format ---> "JSON" is implemented in MS SQL 2016
Well, look :)
It suggested itself :)

-- Делаем таблички
CREATE TABLE story( id int ,[text] text,author_id int )
CREATE TABLE story_comments( id int,s_id INT,[text] text)

-- Заполнем данными

TRUNCATE TABLE [story]
TRUNCATE TABLE [story_comments]

INSERT INTO [dbo].[story]([id],[text],[author_id])VALUES(1,'Статья 1',1)
INSERT INTO [dbo].[story]([id],[text],[author_id])VALUES(2,'Статья 2',2)

INSERT INTO [dbo].[story_comments]([id],[s_id],[text])VALUES(1,1,'Первый комент Первой статьи')
INSERT INTO [dbo].[story_comments]([id],[s_id],[text])VALUES(2,1,'Второй комент Первой статьи')
INSERT INTO [dbo].[story_comments]([id],[s_id],[text])VALUES(3,1,'Третий комент Первой статьи')

-- Сам запрос

;WITH cte 
AS
( 
SELECT [text2]=
(
SELECT 
--s.id     AS 'Story_ID',
       s.[text]  AS 'Story_Text'
     ,
     (
SELECT ISNULL(
REPLACE( 
REPLACE(
REPLACE(
(SELECT [text]=
(SELECT sc.[text]AS 'comment_Text'
FROM [story_comments] sc
WHERE  s.id=sc.[s_id]
FOR XML PATH (''))),'</comment_Text><comment_Text>',',')
,'<comment_Text>','['),'</comment_Text>',']')
,'[NULL]')
)  
FROM   [story] s
FOR XML PATH('')
))

SELECT '['+
REPLACE(
replace (
REPLACE (text2,'<Story_Text>','[')
,'</Story_Text>[',',[')
,'][','],[')+']'
 FROM cte

 Ответ:
 

Answer 4

I would like to know who is doing it, maybe the structure is incorrect?
But I would like it to return like this:
[
[data of the first article, [first comment, second, ...]],
[second article, [...]],
...
]
And one more thing, if there are no comments on the article , the response is returned, but in the fields about the comment everywhere null