0

Complete SQL Server noob here. I'm not sure how to go about finding an answer to as I'm mostly not sure what to begin searching.

I have BLOG table in my SQL Server database that stores blog posts like so

ID Title                             DatePosted
-----------------------------------------------
1  Why Batman is Greater than Sup... 07/15/2017
2  10 Reasons Superman is the wor... 08/02/2017 
3  Sick of Metropolis? Move to Go... 08/03/2017

I have another relational table that stores blogs that users have Liked, i.e.,

UserID  PostID  DateLiked
-------------------------------
232413  2       08/03/2017
232413  1       07/30/2017
234285  2       08/03/2017

Now what I'd like to do is call a simple SELECT * on my BLOG table, but pass in a UserID as an argument to that query to determine if the Blog was liked by said user, so my Result set would look something like so.

Given User ID: 232413

ID Title                             DatePosted  IsLiked
--------------------------------------------------------
1  Why Batman is Greater than Sup... 07/15/2017  1
2  10 Reasons Superman is the wor... 08/02/2017  1
3  Sick of Metropolis? Move to Go... 08/03/2017  0

Is this possible in SQL Server/Database? Any tips or helpful reading is VERY much appreciated!

Improve this question
1
  • Why is Sick of Metropolis? Move to... has IsLiked = 1 ? The post ID liked is 1 and 2. Commented Aug 14, 2017 at 6:16

3 Answers 3

Reset to default
1

Assuming DateLiked column is nullable.
I am casting result of case to bit because I think you want it as Boolean value.
Passing userId parameter as @paramUserId 

DECLARE @paramUserId AS INT;
SELECT b.Id,
       b.Title,
       b.DatePosted,
       CAST(CASE WHEN sb.DateLiked IS NULL THEN 0 ELSE 1) AS BIT) AS IsLiked
FROM BLOG AS b
INNER JOIN storesBlogs AS sb ON b.ID = sb.PostID
WHERE sb.UserID = @paramUserId
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Note: It should be LEFT JOIN, not INNER JOIN and the WHERE clause should be part of the join condition.
Have you tested your code? According to my understanding, if there's no related record in "like" table for this UserId, then the query result should be empty.
Yes result be empty if no user has relation to a blog
Put sb.UserId = @paramUserId as part of where or on condition should have no difference.
I like to specify to parameters in where clause.
|
1

It's the result on sqlite3.

select a.ID, a.Title, a.DatePosted, case UserId when 232413 then 1 else 0 end IsLiked from BLOG a inner join LikeTable b on a.id = b.post_id;

2|10 Reasons Superman is the wor...|08/02/2017|1
1|Why Batman is Greater than Sup...|07/15/2017|1
2|10 Reasons Superman is the wor...|08/02/2017|0

select a.ID, a.Title, a.DatePosted, case UserId when 232413 then 1 else 0 end IsLiked from BLOG a left join LikeTable b on a.id = b.post_id;

1|Why Batman is Greater than Sup...|07/15/2017|1
2|10 Reasons Superman is the wor...|08/02/2017|1
2|10 Reasons Superman is the wor...|08/02/2017|0
3|Sick of Metropolis? Move to Go...|08/03/2017|0

No result satisfies the PO's requirement, but statement#2 worth considering.

Improve this answer

Comments

1 
select id,title,dateposted,0 as IsLiked from blog where id not in (select postid from user_liked where  userid =232413)
union 
select id,title,dateposted,1 as IsLiked from blog where id in (select postid from user_liked where  userid =232413)
order by  IsLiked desc 

Result -

id  title                              dateposted IsLiked

1   Why Batman is Greater than Sup...   2017-07-15 1

2   10 Reasons Superman is the wor...   2017-08-02 1

3   Sick of Metropolis? Move to Go...   2017-08-03 0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.