Databases
StackOverflow2013
Postsdbo
POPL/SQL Comparing Tables
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPL/SQL Comparing Tables
    text
    copied!<p>I have task of matching candidates in my database with suitable job vacancies based on skill and availability, using sql and pl/sql only.</p> <p>I have managed to write the following code which matches available candidates to available vacancies.</p> <pre><code> DECLARE CURSOR availableCandidates_cur IS SELECT * FROM candidate WHERE candidate.available = 'True'; CURSOR availableJobs_cur IS SELECT * FROM position WHERE status = 'Open'; BEGIN DBMS_OUTPUT.PUT_LINE('Available Candidates with matching vacencies'); FOR availableCandidates_rec IN availableCandidates_cur LOOP DBMS_OUTPUT.PUT_LINE('Candidate: ' || availableCandidates_rec.firstName || ' ' || availableCandidates_rec.lastName); FOR availableJobs_rec IN availableJobs_cur LOOP IF (availableCandidates_rec.positionType = availableJobs_rec.positionType) THEN DBMS_OUTPUT.PUT_LINE(availableJobs_rec.positionName); END IF; END LOOP; END LOOP; END; </code></pre> <p>I am struggling to figure out how to now match candidates to positions based on matching skills. The tables in question are</p> <p>candidateSkills</p> <pre><code>candidateID | skillID 1 | 2 1 | 3 2 | 1 3 | 1 3 | 3 </code></pre> <p>positionSkills </p> <pre><code>positionID | skillID 1 | 1 1 | 3 2 | 1 3 | 2 3 | 3 </code></pre> <p>So for example i would like to output that </p> <pre><code>Candidate 1 Matches position 3 Candidate 2 Matches position 2 Candidate 3 Matches position 2 position 3 </code></pre> <p>I fear i may have gone down the wrong path intially which has lead to my confusion.</p> <p>I would appreciate if someone could help steer me in the right direction.</p> <p>Thanks</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