CODEWITHSUNDEEP
mysqlsql
nTuply
nTuply

Reputation: 1364

Grouping Data from Two tables in MySQL

I have 2 tables, posts and posts_flags. posts contains the rows of posts made by the users. Now, if some post gets reported, users have the ability to report the post, thus creating a flag in the posts_flags table. So basically, posts_flags can have multiple flags for the same post.

Now, what I'm trying to do is get the posts rows from the posts table and add a column to show which one has been flagged. To get that column, I am trying to do an INNER JOIN with posts and posts_flags using the post_id. However, I hit an issue now because the posts_flags can have multiple rows for the same post.

How can I get those data? Below is a sample structure of my tables and what I'm trying to achieve.

posts table

| post_id | post_title | post_body |
|    1    |   title a  | something |
|    2    |   title b  | something |
|    3    |   title c  | something |

posts_flags table

| post_id | user_id |
|    1    |   4     | 
|    1    |   5     | 
|    2    |   5     |

What I am trying to achieve

|  post_id | post_title | post_body | flagged
|    1     | title a    | something |   1
|    2     | title b    | something |   1
|    3     | title c    | something |   0

Thank you!

Upvotes: 3

Views: 97

Answers (3)

GMB
GMB

Reputation: 222432

You could use an inline subquery to check if the current post was ever flagged, like:

SELECT
    p.*,
    CASE WHEN EXISTS (
        SELECT 1 FROM posts_flags pf WHERE pf.post_id = p.post_id
    ) THEN 1 ELSE 0 END flagged
FROM posts p

Or better yet:

SELECT
    p.*,
    EXISTS (SELECT 1 FROM posts_flags pf WHERE pf.post_id = p.post_id) flagged
FROM posts p

Demo on DB Fiddle:

| post_id | post_title | post_body | flagged |
| ------- | ---------- | --------- | ------- |
| 1       | title a    | something | 1       |
| 2       | title b    | something | 1       |
| 3       | title c    | something | 0       |

NB: for better performance, you want an index on posts_flags(post_id).

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31772

Count the number of flags in a GROUP BY query, and check if the flag count is greater than zero:

select
  p.post_id,
  p.post_title,
  p.post_body,
  count(f.post_id) > 0 as flagged
from posts p
left join posts_flags f on f.post_id = p.post_id
group by p.post_id, p.post_title, p.post_body

You can also use 

min(f.post_id) is not null as flagged

That might look confusing, but it avoids reading all flags for a post.

Upvotes: 2

Barmar
Barmar

Reputation: 780798

Instead of joining with the whole table, join with a subquery that just returns 1 row per post ID.

It also needs to be a LEFT JOIN rather than INNER JOIN, so the result will have rows for the posts with no flags.

SELECT p.*, pf.post_id IS NOT NULL AS flagged
FROM posts AS p
LEFT JOIN (
    SELECT DISTINCT post_id
    FROM posts_flags) AS pf ON p.post_id = pf.post_id

Upvotes: 4

Related Questions