sql server 2008 r2 - conditional pseudo-column

I have two tables. One of events and one of articles.

Each event has a linked article, but articles can exist without corresponding events.

What I want to do is get a lis开发者_如何学JAVAt of all articles and have a bool pseudo-column that indicates if the article has a linked event or not.

i.e. If exists a row in [Events] where ArticleID = the current ArticleID then true, if not than false.

---

Use a persisted computed column

First create a function to return true or false

-- This function will provide the computed column definition 
CREATE FUNCTION udf_article_has_events ( @id int ) 
RETURNS bit  
WITH SCHEMABINDING
AS 

BEGIN   

DECLARE @retval bit

set @retval = 0
if exists(select * from [Events] where ArticleId = @id) 
    set @retval = 1


RETURN @retval

END

Then add computed column like this

Alter TABLE [dbo.Article] Add HasEvents As dbo.udf_events_exist(id) 

---

Create a view from this

 SELECT *, CASE
             WHEN E.ArticleID IS NULL THEN false 
             ELSE true
           END as EventExist
 FROM Article A
 LEFT JOIN Events E ON A.ArticleID = E.ArticleID

---

If the value must be persisted, you need an after insert and delete trigger on the Event table to update the Article.HasEvents column

CREATE TRIGGER SetHAsEvents
ON dbo.[Events]
FOR INSERT 
AS
   Update Article Set HasEvents = dbo.udf_article_has_events(inserted.ArticleId)
   Where Id = inserted.ArticleId
GO

Added advantage of a persisted column is that it can be indexed