Returning Conditional Columns in SQL Server

Question

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!

Why is `Sick of Metropolis? Move to...` has `IsLiked = 1` ? The post ID liked is 1 and 2. — rcs, Aug 14 '17 at 06:16

score 1 · Answer 1 · answered Aug 14 '17 at 02:58

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

answered Aug 14 '17 at 02:58

Khalil

1,047
4
17
34

Note: It should be `LEFT JOIN`, not `INNER JOIN` and the `WHERE` clause should be part of the join condition. – ZLK Aug 14 '17 at 03:00
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. – tibetty Aug 14 '17 at 03:04
Yes result be empty if no user has relation to a blog – Khalil Aug 14 '17 at 03:06
Put sb.UserId = @paramUserId as part of where or on condition should have no difference. – tibetty Aug 14 '17 at 03:06
I like to specify to parameters in where clause. – Khalil Aug 14 '17 at 03:07
@XihuaDuan There is a huge difference.Check this link [where vs join](https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) – Khalil Aug 14 '17 at 03:09
Sorry for my inaccurate saying, I mean it will return empty result (recordset) or a result comprise of one record (if in biz logic, to like a blog allows only for once) as the where one for this very case. – tibetty Aug 14 '17 at 03:14
BTW, I only talks about where and on, but not left join vs inner join. – tibetty Aug 14 '17 at 03:16
I was replying on difference between where and on. If you want further discussion then transfer it to chat. – Khalil Aug 14 '17 at 03:19
SELECT b.Id, b.Title, b.DatePosted, CAST(CASE WHEN s.UserId = ? THEN 1 ELSE 0) AS BIT) AS IsLiked FROM BLOG AS b LEFT JOIN storesBlogs AS s ON b.ID = s.PostID – tibetty Aug 14 '17 at 03:22
I would use left join only if PostID in second table is nullable which I think is not a possibility .check this [link](https://stackoverflow.com/questions/9798020/when-to-use-left-join-and-when-to-use-inner-join) – Khalil Aug 14 '17 at 03:26

tibetty · Answer 2 · 2017-08-14T03:44:27.703

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.

Anagha · Answer 3 · 2017-08-24T09:39:30.460

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

Returning Conditional Columns in SQL Server

3 Answers3