How can I return a conditional boolean value in SQL

I have a stories table, a users table and a like开发者_JAVA技巧s table.

I want to perform a query on the stories table which will include user_liked (BOOLEAN TRUE or FALSE) based on whether there is a record in the likes table with both the story id and a given user id.

So, select all (and user_liked) from stories where user_liked is true if this user has liked and user_liked is false if not.

Hope that makes sense?

---

The Boolean datatype doesn't exist in MS SQL Serer per se. You should use the bit datetype instead.

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Without knowing your exact schema and database rules I think this may work for you.

Select u.UserId,
       s.StoryId 
       Cast((Case When l.UserId Is Null Then 0 Else 1 End) as bit) as [UserLiked]

From Users u
Left Join Likes l on u.UserId = l.UserId
Left Join Stories s on l.UserId = s.UserId
Where u.UserId = @SomeUserId

---

This seems to work:

SELECT s.*, exists (select l.id from likes l where l.user_id = '1' and l.story_id = s.id) as user_likes FROM stories s;

I had no idea the exists() function existed! ;)

@barry and @Scorpi0 - Thanks for you ideas