Query to select a list of values from a table based on this multiple criteria

Question

I have a table with 2 columns, Ex_Id and Term_Id, both int type. My table will have many Term Ids for one Exercise Id.

     Table would look like this:
      Ex_Id Term_Id
         1     2
         1     3
         1     4
         1     5
         2     2
         3     2
         3     4

etc. Getting a list of Ex_Id is the primary requirement. My function would be like this.

List<int> Get_ExId_List(List<int> lst_TermId)
{
    // return a list of Ex_Id <int>
}

That is, I'll be passing a list of Term Ids and I need to get a list of Exercise Ids back matching some criteria. The criteria to select can be better explained with this pseudo-code: SELECT such Ex_Ids FROM table Exercise_Term WHERE Ex_Id has all the corresponding Term_Ids in the lst_TermId

For eg, from the sample table I provided above,

List<int> Get_ExId_List([2])
{
    // return [1,2,3]
}

List<int> Get_ExId_List([2,4])
{
    // return [1,3]
}

List<int> Get_ExId_List([2,3,4])
{
    // return [1]
}

Query part is my confusion. What would be the query in this condition like? Rest I can manage. Hope question is clear. Thanks..

Answer 1

SELECT Ex_ID 
FROM TableName 
WHERE Term_ID IN (?, ?, ?)                --- (2, 3, 4)
GROUP BY Ex_ID
HAVING COUNT(DISTINCT Term_ID) = 3        --- number of terms in the above list

If the combination (Ex_ID, Term_ID) is unique in the table, you can replace COUNT(DISTINCT Term_ID) with COUNT(*)

This is a relational division problem. The "standard" solution would be using two negatives (NOT EXISTS):

SELECT DISTINCT Ex_ID
FROM TableName e
WHERE NOT EXISTS
        ( SELECT *
          FROM TableName t
          WHERE t.Term_ID IN (?, ?, ?)           --- the list of terms
            AND NOT EXISTS
                  ( SELECT *
                    FROM TableName a
                    WHERE a.Term_ID = t.Term_ID
                      AND a.Ex_ID = e.Ex_ID
                  )
        ) 

or better in your case:

SELECT DISTINCT Ex_ID
FROM TableName e
WHERE NOT EXISTS
        ( SELECT *
          FROM
            ( SELECT ? AS Term_ID  
            UNION
              SELECT ?
            UNION 
              SELECT ?
            ) AS t
          WHERE NOT EXISTS
                  ( SELECT *
                    FROM TableName a
                    WHERE a.Term_ID = t.Term_ID
                      AND a.Ex_ID = e.Ex_ID
                  )
        ) 

---