StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>--All candidates that match every skill in a position select distinct candidateID, positionID from ( --Match candidates and positions, count number of skills that match select candidateID, positionID, skills_per_position ,count(*) over (partition by candidateID, positionID) matched_skills from candidateSkills inner join ( --Number of skills per position select positionID, skillID ,count(*) over (partition by positionID) skills_per_position from positionSkills where status = 'Open' ) positionSkills_with_count on candidateSkills.skillID = positionSkills_with_count.skillID where available = 'True' ) where matched_skills = skills_per_position order by candidateID, positionID; </code></pre> <p>Using these scripts to build the tables:</p> <pre><code>create table candidateSkills as select 1 candidateid, 2 skillID, 'True' available from dual union all select 1 candidateid, 3 skillID, 'True' available from dual union all select 2 candidateid, 1 skillID, 'True' available from dual union all select 3 candidateid, 1 skillID, 'True' available from dual union all select 3 candidateid, 3 skillID, 'True' available from dual; create table positionSkills as select 1 positionID, 1 skillID, 'Open' status from dual union all select 1 positionID, 3 skillID, 'Open' status from dual union all select 2 positionID, 1 skillID, 'Open' status from dual union all select 3 positionID, 2 skillID, 'Open' status from dual union all select 3 positionID, 3 skillID, 'Open' status from dual; </code></pre> <p>However, my results are slightly different. Candidate 3 matches position 1 and 2, not 2 and 3. I hope this is just a typo in your example.</p> <p>Also, I didn't format my output exactly like yours. It can be a bit tricky to have SQL to display results in a multi-line format. However, leaving the SQL unformatted will also make it more useful if you want to use it in some other process.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload