MYSQL query with GROUP_CONCAT option

Question

I have a problem getting proper result from tables.

I have 3 tables:
Table: Articles

|article_id          | article_title   |
|1                   | Title 1         |
|2                   | Title 2         |

Table: Invited_to_read

|user_id             | article_id      |
|10                  | 1               |
|20                  | 1               |
|10                  | 2               |
|10                  | 3               |

Table: readed

|user_id             | article_id      |
|10                  | 1               |
|20                  | 1               |
|30                  | 1               |
|20                  | 2               |

What I want to get information if everybody who was invited also read article, result must be true, otherwise false.

For example if I want to get information for article_id = 1, then I should get TRUE as ll invited users readed it.
For example if I want to get information for article_id = 2, then I should get FALSE as user at least one user who invited not confirmed read.
For example if I want to get information for article_id = 3, then I should get FALSE as there is no any user confirmed read.

If some user, who is not invited for reading article confirm it, its not important at all.
So bottom line. I need to know if all invited users readed article or not.
Thank you.

Just write a query that tests if there's any row in `invited_to_read` that doesn't have a corresponding row in `readed`. You can do this with `LEFT JOIN` or with `NOT EXISTS`. — Barmar, Jan 25 '20 at 12:24

score 0 · Accepted Answer · answered Jan 25 '20 at 12:27

You can get the results you want by LEFT JOINing the Invited_to_read table to the readed table and comparing the count of users invited to read an article to the count of those users who have read the article:

SELECT i.article_id,
       COUNT(i.user_id) = COUNT(r.user_id) AS all_read
FROM Invited_to_read i
LEFT JOIN readed r ON r.user_id = i.user_id AND r.article_id = i.article_id
GROUP BY i.article_id
ORDER BY i.article_id

Output (for your sample data):

article_id  all_read
1           1
2           0
3           0

Demo on SQLFiddle

This is what I was searching for... I was in wrong direction. Using GROUP_CONCAT was Idea just because I need it also to show exact id of readed... and this was wrong approach. Thanks. — Aljosa, Jan 25 '20 at 13:42

MYSQL query with GROUP_CONCAT option

1 Answers1