This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mysql intersect

 
seema prakash
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
  • 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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
  • 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
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic