wood burning stoves 2.0*
The moose likes JDBC and the fly likes mysql intersect Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "mysql intersect" Watch "mysql intersect" New topic
Author

mysql intersect

seema prakash
Ranch Hand

Joined: Nov 17, 2004
Posts: 59
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

Joined: May 26, 2003
Posts: 30596
    
154

Seema,
You could use an or clause or the union operator:





[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
seema prakash
Ranch Hand

Joined: Nov 17, 2004
Posts: 59
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

Joined: May 26, 2003
Posts: 30596
    
154

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.
 
GeeCON Prague 2014
 
subject: mysql intersect