troubleshooting Question

Using Sub-Query within a Select...Case

Avatar of Karl_mark
Karl_mark asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
8 Comments1 Solution551 ViewsLast Modified:
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

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?
ASKER CERTIFIED SOLUTION
Zberteoc

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros