469,620 Members | 1,857 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,620 developers. It's quick & easy.

intersect in mysql

Can any one give an alternative to this oracle query in mysql
Expand|Select|Wrap|Line Numbers
  1. Select s.EMP_CODE,fname from profilesys.skill_emp_master P join profilesys.skill_master_new R on ( P.Skill_id = r.skill_id) 
  2. join profilesys.employee_master s on(p.EMP_CODE = s.EMP_CODE) 
  3. AND UPPER(coalesce(R.STATUS,'WORKING')) NOT IN ('RETIRED','RESIGNED','ABSCONDING') 
  4. where (PROFICIENCY_LEVEL >= '"+skillLevel+"' and p.skill_id = '"+skill+"' and PROJECT_EXPERIENCE >= '"+exp+"')
  5.  
  6. intersect
  7.  
  8. Select s.EMP_CODE,fname from profilesys.skill_emp_master P join profilesys.skill_master_new R on ( P.Skill_id = r.skill_id) 
  9. join profilesys.employee_master s on(p.EMP_CODE = s.EMP_CODE) 
  10. AND UPPER(coalesce(R.STATUS,'WORKING')) NOT IN ('RETIRED','RESIGNED','ABSCONDING') 
  11. where (PROFICIENCY_LEVEL >= '"+skillLevel2+"' and p.skill_id = '"+skill2+"' and PROJECT_EXPERIENCE >= '"+exp2+"')
  12.  
  13. intersect
  14.  
  15. Select s.EMP_CODE,fname from profilesys.skill_emp_master P join profilesys.skill_master_new R on ( P.Skill_id = r.skill_id) 
  16. join profilesys.employee_master s on(p.EMP_CODE = s.EMP_CODE) 
  17. AND UPPER(coalesce(R.STATUS,'WORKING')) NOT IN ('RETIRED','RESIGNED','ABSCONDING') 
  18. where (PROFICIENCY_LEVEL >= '"+skillLevel3+"' and p.skill_id = '"+skill3+"' and PROJECT_EXPERIENCE >= '"+exp3+"')
  19.  
Nov 1 '07 #1
1 1522
Expand|Select|Wrap|Line Numbers
  1. Select s.EMP_CODE,fname from profilesys.skill_emp_master P join profilesys.skill_master_new R on ( P.Skill_id = r.skill_id)  
  2. join profilesys.employee_master s on (p.EMP_CODE = s.EMP_CODE) 
  3. AND UPPER(coalesce(R.STATUS,'WORKING')) NOT IN ('RETIRED','RESIGNED','ABSCONDING') 
  4. where (PROFICIENCY_LEVEL >= '1' and p.skill_id = '2' and PROJECT_EXPERIENCE >= '3')
  5.  
  6. AND     EXISTS(
  7. Select l.EMP_CODE,fname from profilesys.skill_emp_master P join profilesys.skill_master_new R on ( P.Skill_id = r.skill_id)  
  8. join profilesys.employee_master l on (p.EMP_CODE = l.EMP_CODE) 
  9. AND UPPER(coalesce(R.STATUS,'WORKING')) NOT IN ('RETIRED','RESIGNED','ABSCONDING') 
  10. where (PROFICIENCY_LEVEL >= '2' and p.skill_id = '3' and PROJECT_EXPERIENCE >= '3')
  11. AND    (s.EMP_CODE  = l.EMP_CODE) AND    (s.fname  = l.fname))
  12.  
  13. AND     EXISTS(
  14. Select q.EMP_CODE,fname from profilesys.skill_emp_master P join profilesys.skill_master_new R on ( P.Skill_id = r.skill_id)  
  15. join profilesys.employee_master q on (p.EMP_CODE = q.EMP_CODE) 
  16. AND UPPER(coalesce(R.STATUS,'WORKING')) NOT IN ('RETIRED','RESIGNED','ABSCONDING') 
  17. where (PROFICIENCY_LEVEL >= '1' and p.skill_id = '4' and PROJECT_EXPERIENCE >= '3')
  18. AND    (q.EMP_CODE  = s.EMP_CODE) AND    (q.fname  = s.fname))
  19.  
Nov 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Nick | last post: by
3 posts views Thread by prathamesh.deshpande | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.