SQL query to return results from one to many table-CodePudding

I'm having difficulties trying to return some data from a poorly structured one to many table.

I've been provided with a data export where everything from 'Section Codes' onwards (in cat_fullxPath) relates to a 'skillID' in my clients database.

The results previously returned on one line but I've used a split function to break these out (from the cat_fullXPath column). You can see the relevant 'skillID' from my clients DB in the far right column:

From here, there are thousands of records that may have a mixture of these skillIDs (and many others, I've just provided this one example). I want to be able to find the records that match all 4 (or however many match from another example) skillIDs and ONLY those.

For example (I just happen to know this ID gives me the results I want):

SELECT
     id
     skillID
FROM table1
WHERE skillID IN ( 1004464, 1006543, 1004605, 1006740 )
AND id = 69580;

This returns me:

Note that these are the only columns in that table.

So this is an ID I'd want to return.

These are results I'd not want to return as one of the skillIDs are missing:

I've created a temp table with a count of all the skills for each ID but I'm not sure if I'm going down the right path at this point

I'm pretty sure that there's a simple solution to this, however I'm hitting my head against the wall. Hope someone can help!

CodePudding user response：

We know that you need exactly 4 skills, so just make a subquery:

select id from 
(
     SELECT
       id
       count(skillID) countSkill
     FROM table1
     WHERE skillID IN ( 1004464, 1006543, 1004605, 1006740 )
     group by id;
)
where countSkill = 4;

Could work with sum, instead of count. But instead of filtering by the 4, you filter by 4022352, which is the sum of all skillID.

You can also remove the subquery and use HAVING. But you will obtain worse performance.

     SELECT
       id
       count(skillID) countSkill
     FROM table1
     WHERE skillID IN ( 1004464, 1006543, 1004605, 1006740 )
     group by id
     having count(skillID) = 4;

CodePudding user response：

You haven't told us your DBMS. Here is a standard SQL approach:

select id
from table1
group by id
having count(case when skillid = 1004464 then 1 end) > 0
   and count(case when skillid = 1006543 then 1 end) > 0
   and count(case when skillid = 1004605 then 1 end) > 0
   and count(case when skillid = 1006740 then 1 end) > 0
   and count(case when skillid not in (1004464, 1006543, 1004605, 1006740) then 1 end) = 0;