CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user3201333
user3201333

Reputation:

Select 2 time records from one table

I have 2 table like below

PostList: post_id(PK,Identity)

LikeList: like_id(PK,Identity), post_id(FK)

Every entry of post in LikeList, is liked.

This task like post on FaceBook

I am passing 2 value from .cs, when DataList Binding Whichever post_id and Current User id(mid)

  1. Count every post like.
  2. if current user alredy liked, then change button text Unlike

I create query like,

SELECT mid
,(SELECT COUNT(post_id) FROM LikeList WHERE post_id=@sp_post_id) as like_count
FROM LikeList WHERE (post_id=@sp_post_id AND mid=@sp_mid) 
GROUP BY mid

I got post_id count but in below case if past post_id=27 and user/mid=2 then obviously we not found record.

What I want: Check post likes count , check if user like this post or not. (how i check counts and current user like or not?)

Table Data:

PostList

post_id fid   mid   post_img         post_msg                      post_time

1       1     1     Tulips.jpg       this post from user1.         2015-05-26
3       3     2     Lighthouse.jpg   this post from user2.         2015-05-26
5       1002  3     road1.jpg        this post from user3.         2015-05-26
6       3     2     map1.jpg         this 2 post from user2.       2015-05-27
7       1     1                      This is text Post From User1  2015-05-27
26      1     1     globe.jpg                                      2015-05-28
27      1003  5     Gujarat.jpg      this post from user5, again.  2015-05-29
30      3     2     Location2.jpg    post from user2               2015-05-29

LikeList

like_id  post_id    mid like_status like_time
1        27         1   1           2015-05-29
2        30         1   1           2015-05-29
3        6          1   1           2015-05-29
4        30         2   1           2015-05-29
9        6          2   1           2015-05-29

What will happen: When user online: mid=1

post_id:30 likes 2 times (online user like too)

When user online: mid=2

post_id:27 likes 1 time (online user not like it)

Upvotes: 0

Views: 84

Answers (1)

Eric
Eric

Reputation: 5743

SELECT 
    post_id, 
    COUNT(*) AS LikeCount,
    MAX(CASE mid WHEN @sp_mid THEN 1 ELSE 0 END) AS UserLiked -- 0 for No; 1 for Yes
FROM LikeList 
WHERE 1 = 1
    AND post_id = @sp_post_id -- You can comment this line for viewing all post
    AND like_status = 1
GROUP BY post_id

Upvotes: 1

Related Questions