Using Sub-Query within a Select...Case

Karl_mark
Karl_mark used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this:

select distinct
	p.[title],
	case 
		when j.POST_ID is null then 1
		else 0
	end			as FK_Check
from 
	post p
	left join job j
		on j.POST_ID=p.POST_ID

Open in new window


Normally I would put 1 if exists and 0 if doesn't but I followed your request.
Top Expert 2010

Commented:
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.
Top Expert 2010

Commented:
Please disregard my post.  Same logic as Zberteoc's :)
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

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

Commented:
Edited, content I posted was already there
Top Expert 2010

Commented:
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

Commented:
@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)

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial