CODEWITHSUNDEEP
sqlsql-server
Adnan Yaseen
Adnan Yaseen

Reputation: 853

Return true or false as custom column based on condition

This might be very simple to achieve but I have not done this before and I need some guide or help in achieving this.

I have created a stored procedure and passing the UserId to the procedure. I have to modify my query and want a custom column to be returned in the query which will be either true' or 'false'. True or false will be determined whether User has commented for the Post or not. The pseudo code of the desired query is as follows,

SELECT 
    a.Id, 
    a.Title, 
    a.Description, 
    b.Id, 
    b.Comment, 
    ['true' or 'false' as Commented based on whether User has commented for the respective post or not] 
from tbl_Posts a 
LEFT OUTER JOIN tbl_PostsComment 
    b ON a.Id = b.PostId

TABLES

tbl_Users
---------------
Id
Username
FirstName
LastName
Address


tbl_Posts
---------------
Id
Title
Description


tbl_PostsComment
------------------
Id
PostId
UserId
Comment

Thanks

Edit:

To help better explain the question I will describe the scenario. In my website when the Users log in then they will see all posts and their comments. So records are selected using the query that I have posted above. Now their is a button with every post "Give comments". By clicking this user will be able to give the comments for any respective post but only if he has not already given the comments.

So what I want to do is to select all the records as usual but now I am passing the UserId to the stored procedure. All posts will be selected but if for any post User has already given comments (If Comments for that post will be present in the tbl_PostsComment with UserId of the User) then true will be returned otherwise false will be returned.

Upvotes: 1

Views: 4771

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Maybe this one will work:

SELECT
    a.Id,
    a.Title,
    a.Description,
    HasCommented = CASE WHEN COUNT(c.Id) > 0 THEN 'True' ELSE 'False' END
FROM tbl_Posts p
LEFT JOIN tbl_postsComment c
    on c.PostId = p.Id
    AND c.UserId = @userId
GROUP BY
    a.Id, a.Title, a.Description

Upvotes: 4

rory.ap
rory.ap

Reputation: 35270

Is this what you're looking for?

SELECT a.Title, a.Description, b.ID, b.Comment CASE WHEN b.Comment IS NOT NULL THEN 1 ELSE 0 END AS [Commented]
FROM tbl_Posts a LEFT OUTER JOIN tbl_PostsComment b ON a.Id = b.PostId

Upvotes: 0

Related Questions