Link to home
Start Free TrialLog in
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?
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
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
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.
Please disregard my post.  Same logic as Zberteoc's :)
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

Edited, content I posted was already there
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
@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)
Avatar of Karl_mark
Karl_mark

ASKER

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