To continue the sample?

Hello, please tell me, I need to choose news those of users whose number of reviews > 1 as a result of this I have 3 tables: comments, news, users

The structure of the table users:
-id
-login
-data
The structure of the table news:
-id
-user_id
-title
-text
Structure table comments:
-id
-user_id
-news_id
-text

For example here is a table filled with comments like this:
5deffdb18df49716268031.png
Doing such a query:
SELECT comments.user_id
FROM comments
GROUP BY comments.user_id
HAVING COUNT(comments.user_id) > 1

Get the result:
5deffe097cb86238176679.png

Everything seems right, the query returns the id of those users who left more than one comment, but I need another job to bring them news, and please tell me how you can still find the news of these users ? I understand that I need to dig in the direction of join and subquery ?

Thank you for your answer.
April 3rd 20 at 17:23
1 answer
April 3rd 20 at 17:25
Solution
Hello
Joiny not as scary as they seem, the main thing is practice.
SELECT
USERS.LOGIN
NEWS.TITLE
NEWS.TEXT
FROM
USERS
LEFT OUTER JOIN
COMMENTS
ON
 USERS.ID = COMMENTS.USER_ID
RIGHT OUTER JOIN
NEWS
ON
 USERS.ID = NEWS.USER_ID
GROUP BY
NEWS.TITLE
NEWS.TEXT
USERS.LOGIN
HAVING
 (COUNT(COMMENTS.ID) > 1)

p.s. To learn how to write queries to write queries.
=))) thank you for the reply, only had to slightly adjust the query:
SELECT news.id ID, news.title title, users.login LOGIN
FROM users
LEFT OUTER JOIN comments ON users.id = comments.user_id
RIGHT OUTER JOIN news ON users.id = news.user_id
GROUP BY comments.user_id, news.title, news.id
HAVING COUNT(comments.user_id) > 1

The initial query is all news user led, as I understand it due to the fact that I have in database have 2 news with the same title, and in the end, if you have a news title to change, then everything is fine. If you're interested here is the link to the base, she is the one I worked with, she's super small, it's job to protect the lab at uni)
A link to Yandex.Disk - bernadette commented on April 3rd 20 at 17:28
@bernadette,
understand
GROUP BY - combines identical strings in the sample one(this is used)
that never happened, add to selection SELECT any unique field - ID news, for example, in this case, even if the title and the news text is the same they don't connect.

SELECT
 USERS.LOGIN AS USER_LOGIN
NEWS.ID AS NEWS_ID
NEWS.TITLE AS NEWS_TITLE
NEWS.TEXT AS NEWS_TEXT
FROM
USERS
LEFT OUTER JOIN
COMMENTS
ON
 USERS.ID = COMMENTS.USER_ID
RIGHT OUTER JOIN
NEWS
ON
 USERS.ID = NEWS.USER_ID
GROUP BY
NEWS.TITLE
NEWS.TEXT
USERS.LOGIN
NEWS.ID
HAVING
 (COUNT(COMMENTS.ID) > 1)
- Kim_Jones commented on April 3rd 20 at 17:31
@bernadette, at work I have restricted access to Yandex, Google mail, and all that, to come can, only at home. - Kim_Jones commented on April 3rd 20 at 17:34

Find more questions by tags SQL