Avatar of Karl_mark
Karl_mark
 asked on

Using Sub-Query within a Select...Case

I'm trying to return a value using a Select Case statement.

Basically, I want to return a value of 1 if a related record exists in another table for the record in the current table.
For example, There is a field called POST_ID in the POST table. The POST_ID is a foreign key in the JOB table. I write a query on the POST table and if the POST_ID for that record does not exist in the POST_ID column on the JOB table then it means that the POST is currently vacant.
Something along the lines of:

select [title], CASE(select jobtitle from JOBDETAIL join post)
	when  null then 1
	else 0
	end
from
	POST

Open in new window


I know this is not correct, but I know how the CASE statement works normally, but I'm really trying to replicate NOT IN(jobtitle from jobdetail).
Or, would in be easier to use RIGHT JOIN?
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
Karl_mark

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Zberteoc

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

select [title], CASE WHEN j.jobtitle IS NULL THEN 1 ELSE 0 END
from
      POST p left join
        JOBDETAIL j ON p.ID = j.PostID


Not sure what the ON expression should be.  I would do an outer join rather than CASE for this.
Patrick Matthews

Please disregard my post.  Same logic as Zberteoc's :)
Zberteoc

You can also do this:

select 
	p.[title],
	case 
		when exists (select * from job where POST_ID=p.POST_ID) then 0
		else 1
	end			as FK_Check
from 
	post p

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jogos

Edited, content I posted was already there
Patrick Matthews

jogos,

With the exception of qualifying the table names who the schema name dbo, how exactly does your post improve upon the virtually identical post Zberteoc made 27 minutes before you posted?

Patrick
jogos

@matthewspatrick
it did not and as you can see I already corrected my duplicate post (was mistaken with a newly opened tab with this that already was open)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Karl_mark

ASKER
Thanks Zberteoc. You're absolutely right about the 1 and 0; I was typing in a rush! Works perfectly.