• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

mysql intersect

 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
I have a table candidateSkills where I store the skillName
and skillDuration. The table looks like:

skillid | ReumeId| skillName | skillDuration |
---------------------------------------------
1. | 1 | java | 32 |
2. | 1 | jsp | 43 |
3. | 1 | oracle | 34 |
4. | 2 | jsp | 48 |
5. | 2 | oracle | 83 |
6. | 3 | jsp | 67 |

I want to query such that I get the resumeid where
the skillName is jsp and the skill duration is > 10
and the skill name is oracle and skillDuration is > 15.

While I was using oracle, the sql query that worked was

SELECT ResumeId from candidateSkills where skillName = 'jsp' AND
skillDuration > 10
INTERSECT
SELECT ResumeId from candidateSkills where skillName = 'oracle' AND
skillDuration > 15

The output was [1, 2]

But, I see that mysql does not support intersect. How do I go about ?
Please advice.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Seema,
You could use an or clause or the union operator:



 
seema prakash
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jeanne Boyarsky,
Thanks for the reply. But to my understanding on using the or operator I would get a result even if one condition i.e with one skill name and skill duration is satisfied. But I need a result which would return the resumeid
thta would have both the skillnames and their respective durations
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Seema,
Sorry, I missed that! You need to use a nested query to do the and/intersect. For example,



Note that I haven't tried this in mySql, so the syntax may vary slightly. But the idea is the same.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic