Link to home
Start Free TrialLog in
Avatar of 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

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?
Avatar of Zberteoc
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select [title], CASE WHEN j.jobtitle IS NULL THEN 1 ELSE 0 END
      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.
Please disregard my post.  Same logic as Zberteoc's :)
You can also do this:

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

Open in new window

Edited, content I posted was already there

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?

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)
Avatar of Karl_mark


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