CODEWITHSUNDEEP
sqlsql-server
Scott Nimrod
Scott Nimrod

Reputation: 11570

How do I return a boolean field value based on if there's a reference in another table?

I have the following query:

let getLinkTopicsSql = "SELECT     Topic.Id, Topic.Name
                        FROM       Topic
                        INNER JOIN LinkTopic
                              ON   LinkTopic.TopicId = Topic.Id
                        INNER JOIN Link
                              ON   LinkTopic.LinkId = Link.Id
                        WHERE  Link.Id = @LinkId"

I need to update the fields returned from:

SELECT     Topic.Id, Topic.Name

to

SELECT     Topic.Id, Topic.Name, IsFeatured

However the IsFeatured custom field that I want to return is determined to be true only if the record exists in the FeaturedTopic table.

The FeaturedTopic table has a column called TopicId.

As a result, I want to add another INNER JOIN to the query. However, I know that that will ignore the links that are not featured that still need to be returned.

Question:

How do I return a boolean field value based on if there's a reference in another table?

Upvotes: 1

Views: 1971

Answers (1)

Patrick Artner
Patrick Artner

Reputation: 51683

Read up on outer joins (link), esp. left outer joins. That will enable you to keep the left parts of the query so far and join either the values in IsFeatured or get null values for them.

LEFT OUTER JOIN

SELECT     Topic.Id, Topic.Name, 
    case when FeaturedTopic.TopicId is null then 0 else 1 end as IsFeatured
FROM       Topic
INNER JOIN LinkTopic
              ON   LinkTopic.TopicId = Topic.Id
INNER JOIN Link
              ON   LinkTopic.LinkId = Link.Id
LEFT OUTER JOIN FeaturedTopic 
              ON   Topic.Id = TopicId
WHERE  Link.Id = @LinkId"

SUBQUERY

Another way is a subquery (shaky on syntax) which might be slower on huge sets then the left outer join solution:

SELECT     Topic.Id, 
           Topic.Name, 
           isnull( (select 1 from FeaturedTopic where TopicId = Topic.Id),0) as IsFeatured 
FROM       Topic
INNER JOIN LinkTopic
              ON   LinkTopic.TopicId = Topic.Id
INNER JOIN Link
              ON   LinkTopic.LinkId = Link.Id
WHERE  Link.Id = @LinkId"

Or something along the lines of 

case when exists (select 1 from FeaturedTopic where FeaturedTopic.TopicId = Topic.Id) then 1 else 0 end as IsFeatured

The exact syntax depends on the flavour of DB you use (Oracle, TSql, MYSql, ...)

Upvotes: 2

Related Questions