CODEWITHSUNDEEP
mysqlsql
user784637
user784637

Reputation: 16152

How to to join a table onto itself, grouping the field being joined on?

I have a table that creates a record each time a post is created and another table that creates a record each time a post is favorited.

CREATE TABLE `posts` (
  `post_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`post_id`)
)

CREATE TABLE `favorited_posts` (
  `user_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  KEY `user_id` (`user_id`),
  KEY `post_id` (`post_id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`)
)

The following query returns a result set that shows how many times a post has been favorited

SELECT p.post_id, p.title,
    COUNT(fp.post_id) as num_favorites
FROM posts p
LEFT JOIN favorited_posts fp
ON fp.post_id = p.post_id
WHERE p.post_id BETWEEN 0 AND 10000
GROUP BY 1;

+-------------------+----------------+
| post_id  | title  |  num_favorites |
+-------------------+----------------+
| 1        | abc    |  15            |
| 2        | hello  |  0             |
| 3        | test   |  7             |
+----------+--------+----------------+

How can I change the query to create an ad-hoc column with a true/false, 0/1 value that represents whether a user has favorited a particular post? It would produce a result set like this (The user has favorited post 2 and 3)

+-------------------+----------------+---------------+
| post_id  | title  |  num_favorites | has_favorited |
+-------------------+----------------+---------------+
| 1        | abc    |  15            | 0             |
| 2        | hello  |  0             | 1             |
| 3        | test   |  7             | 1             |
+----------+--------+----------------+---------------+

I can retrieve the posts a particular user has favorited with the query but do not know how to retrieve this information as part of a single query that captures the post_id, title and total number of favorites.

SELECT * FROM favorited_posts
WHERE user_id = 10;

Upvotes: 1

Views: 44

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

You can use conditional aggregation:

SELECT p.post_id,
       p.title,
       COUNT(fp.post_id) as num_favorites,
       sum(case when fp.user_id = 10 then 1 else 0 end) as has_favorited
  FROM posts p
  LEFT JOIN favorited_posts fp
    ON fp.post_id = p.post_id
 WHERE p.post_id BETWEEN 0 AND 10000
 GROUP BY p.post_id, p.title

This query is the same as what you have now except it shows a column that indicates whether or not the given user (specified in the case statement) has favorited the particular post on the given row.

Upvotes: 1

Sebas
Sebas

Reputation: 21542

Just use the second query in the left join:

SELECT p.post_id, p.title, IF(COUNT(fp.post_id)>0, 'True', 'False') as user_favorite
FROM posts p
LEFT JOIN (
        SELECT post_id FROM favorited_posts
        WHERE user_id = 10
    ) fp ON fp.post_id = p.post_id
WHERE p.post_id BETWEEN 0 AND 10000
GROUP BY 1;

Also, you miss a foreign key in favorited_posts to the user table.

Upvotes: 1

Related Questions