Remove where clause from query depending on a specific value

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 866 Views - Last Post: 03 August 2016 - 10:24 AM

#1 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Remove where clause from query depending on a specific value

Posted 03 August 2016 - 08:31 AM

I'm writing a query to get a list of employee which is eligible for a post.
I have a table of employee with the following field:
Empid , name ,gender , address.

Then I have a table application with the following field:
Appid , emp id, description ,reqid

Last table is requirement with the following field:
Reqid , gender , experience

Query:
Select * from employee emp
inner join on application app where app.empid = emp.empid
Inner join on requirement r where r.gender = emp.gender

My problem is sometimes the gender in table requirement is notapplicable which means this post is applicable for everyone irrespective of the gender.
If it is male and female the query works fine. But if it is notapplicable, i should ignore the last where clause in the query. How can i do this in oracle sql?

Is This A Good Question/Topic? 0
  • +

Replies To: Remove where clause from query depending on a specific value

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,117
  • Joined: 12-June 08

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 08:43 AM

 Inner join on requirement r where r.gender = emp.gender

Why wouldn't this be on the shared key, reqid?
Was This Post Helpful? 0
  • +
  • -

#3 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 08:46 AM

View Postmodi123_1, on 03 August 2016 - 08:43 AM, said:

 Inner join on requirement r where r.gender = emp.gender

Why wouldn't this be on the shared key, reqid?

What do you mean by shared key?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,117
  • Joined: 12-June 08

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 08:54 AM

Table 'application' and table 'requirements' have a shared key, right?
Was This Post Helpful? 0
  • +
  • -

#5 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 08:58 AM

View Postmodi123_1, on 03 August 2016 - 08:54 AM, said:

Table 'application' and table 'requirements' have a shared key, right?

Yes reqid
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,117
  • Joined: 12-June 08

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:02 AM

So the join should be on *THAT* key.. then use the WHERE clause to limit by other conditions.
Was This Post Helpful? 0
  • +
  • -

#7 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:13 AM

View Postmodi123_1, on 03 August 2016 - 09:02 AM, said:

So the join should be on *THAT* key.. then use the WHERE clause to limit by other conditions.

Sorry I wrongly wrote the query. here it is:

Select * from employee emp
inner join application app on app.empid = emp.empid
Inner join requirement r on r.reqid = app.reqid
where r.gender = emp.gender;

If gender is 'notapplicable' in table requirement , i should remove the where clause.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,117
  • Joined: 12-June 08

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:19 AM

How would 'not applicable' be shown? Is that an empty value?
Was This Post Helpful? 0
  • +
  • -

#9 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:21 AM

the value itself in database is :
notapplicable = na
male = m
female = f
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,117
  • Joined: 12-June 08

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:25 AM

Okay then think about it in a compound way.. the first half has to be true for the second half to be evaluated.

((IF gender IS NOT NA) AND (IF gender matches the requirements))
Was This Post Helpful? 0
  • +
  • -

#11 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:29 AM

View Postmodi123_1, on 03 August 2016 - 09:25 AM, said:

Okay then think about it in a compound way.. the first half has to be true for the second half to be evaluated.

((IF gender IS NOT NA) AND (IF gender matches the requirements))



Ok agree withe first part..

How about the if statement?How should i write this in my query?
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,117
  • Joined: 12-June 08

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:30 AM

You only want ones that match.. so.. does columnA match columnB.
Was This Post Helpful? 0
  • +
  • -

#13 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:31 AM

Yes this is what I want. then..?
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13563
  • View blog
  • Posts: 54,117
  • Joined: 12-June 08

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:41 AM

I am not sure what you are asking.
Was This Post Helpful? 0
  • +
  • -

#15 alinabb  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-August 16

Re: Remove where clause from query depending on a specific value

Posted 03 August 2016 - 09:47 AM

Ok in my employee table I will have a list of employee with gender either m or f. In my table requirement, gender can be na, or m or f. Because a post can require all employees both male and female which mean na. Sometimes it can require only m or f. How do i cater this situation in my query?
Currently my query is taking "where r.gender = emp.gender" ,

if r.gender = f or r.gender = m, it will work fine.But if r.gender = na , it will not work since in table employee , i don't have anyone with this gender. if r.gender = na , it should return both female and male.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2