CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user2924127
user2924127

Reputation: 6240

Conditional pure sql query

This is a simple example of the problem I am facing. I have two tables, Job and Person. A Person can work at 1 or more jobs. I need to return one column of data with the job ids of a particular person, but if the person works at job_id = 'god', then it should return all the job ids. I am using a framework and this must be done in a SQL statement and not PLSQL(which is why I am struggling with this).

**JOB**

JOB_ID | JOB_NAME
doc       Doctor
ba        Barber
god       Admin

**PERSON**

PERSON_ID | JOB_ID
 Jeff       doc
 Jeff       ba
 Mary       ba
 Thor       god

So far I am able to return all the jobs where a user belongs to, but I need to somehow return all the jobs if in the person person has a job_id ='god.'

select DISTINCT p.JOB_ID
  from PER_JOBS p,PER_PEOPLE pu
WHERE
p.JOB_ID= pu.JOB_ID
and pu.PERSON_ID = 'thor';

So if if I select Jeff: doc and ba should be returned while if I select Thor: doc,ba,and god should be returned.

Upvotes: 2

Views: 290

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I think the fastest way to approach this is with two exists clauses:

select j.*
from job j
where exists (select 1 from person p where p.job_id = j.job_id) or
      exists (select 1 from person p where p.job_id = 'god');

This can take advantage of an index on person(job_id). And, because duplicates do not need to be removed, it should return much faster than a select distinct solution.

Upvotes: 0

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

Here is the query:

select distinct j.job_id from job j join person p
on ((j.job_id = p.job_id and p.job_id <> 'god') or p.job_id = 'god')
where p.person_id = 'Thor';

JOB_ID
----------
doc
ba
god

select distinct j.job_id from job j join person p
on ((j.job_id = p.job_id and p.job_id <> 'god') or p.job_id = 'god')
where p.person_id = 'Jeff';

JOB_ID
----------
doc
ba

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You need to do a join here and then add a where clause to get what you want. The join look like this

SELECT P.PERSON_ID, J.JOB_NAME
FROM PERSON P
JOIN JOB J ON (P.JOB_ID = J.JOB_ID) OR P.JOB_ID = 'god'

This will give you a list of people and their jobs. You could even make this a view.

Now just select the person you want

SELECT P.PERSON_ID, J.JOB_NAME
FROM PERSON P
JOIN JOB J ON (P.JOB_ID = J.JOB_ID) OR P.JOB_ID = 'god'
WHERE P.PERSON_ID = 'thor';

Upvotes: 3

Related Questions