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
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?