How to find exact match of a set of values?-CodePudding

I have a simple table storing teacher-student relationships to show who's class students are taking or who the teacher is teaching. Either way. (Sorted by Teacher for readability)

CREATE TABLE TS_RELATIONSHIP
(
    Teacher NVARCHAR(10);
    Student NVARCHAR(10);
)

Example:

Teacher Student
-------------------
Conner     Yumi
Conner     Shawn
Conner     Casey
Ericson    Eric
Ericson    Yumi
Ericson    Sue
Ericson    Lin
Johnson    Shawn
Johnson    Lin
Johnson    Ivan
Johnson    Casey
Johnson    Gina

Now if I have a set of student names, how can I find an "exact match" of the data? (Order does not matter)

For example, if the set of student names is Casey, Gina, Ivan, Lin, Shawn, then I know Johnson is the teacher I'm looking for, anything other than these five people (exactly this five, not one student short or more), nothing will be returned (unless other teachers got the exact match).

I tried to do it with the help of C#, and did something like,

string[] studentNames;
DataTable dt = SQL.GetDT(@"SELECT Teacher FROM [TS_RELATIONSHIP] WHERE Student=@p0;",studentNames[0]);    //Assuming SQL.GetDT is a function that execute the T-SQL and return it as a DataTable.
for(int i=1;i<studentNames.Length;i  )
{
    string teacherNames = string.Join(",", dt.AsEnumerable().Select(x => string.Format($"'{x.Field<string>("Teacher")}'")));
    dt=SQL.GetDT(@"SELECT Teacher FROM [TS_RELATIONSHIP] WHERE Student=@p0 and Teacher IN (@p1);",studentNames[i], teacherNames);
}

But soon find out that this method won't return an "exact" match!

As long as the Teacher "contains" the student list, it will be returned as the answer (which is incorrect).

Could somebody please be so kind and teach me how to do it correctly?

Either in pure T-SQL or with the help of C# is fine.

Thank you very much for your help!

CodePudding user response：

This is a problem known as Relational Division Without Remainder. There are a number of solutions. Here is one:

Firstly, you must pass all your values to SQL as a table, for which you will need a Table Valued Parameter (shown below for C#).

I'm going to assume that TS_RELATIONSHIP is unique over the two columns, and that the input list of students is unique also

SELECT
  ts.Teacher
FROM TS_RELATIONSHIP ts
LEFT JOIN @students s ON s.Value = ts.Student
GROUP BY
  ts.Teacher
HAVING COUNT(*) = @studentCount
   AND COUNT(s.Value) = @studentCount;

What this does is take all the rows from the table, left-join it to the input list, group it by Teacher and ensure that the total number of rows is equal to the total number of inputs, and that the number of matched inputs is also exactly equal to that total.

You need to create a Table Type, I usually have a few standard one-column ones to use:

CREATE TYPE dbo.StringList AS TABLE (Value nvarchar(250) NOT NULL PRIMARY KEY);

To pass in the full list as a TVP you can do as follows:

var dtInput = new DataTable();
dtInput.Columns.Add("Value", typeof(string));
foreach (var student in studentNames)
    dtInput.Rows.Add(student);

var dtResult = new DataTable();
using (var conn = new SqlConnection(yourConnString))
using (var comm = new SqlCommand("THE ABOVE QUERY"))
{
    comm.Parameters.Add("@studentCount", SqlDbType.Int).Value = studentNames.Length;
    comm.Parameters.Add(new SqlParameter("@students", SqlDbType.Structured) { TypeName = "dbo.StringList", Direction = ParameterDirection.Input, Value = dtInput});

    conn.Open();
    using (var reader = comm.ExecuteReader())
        dtResult.Load(reader);
}

CodePudding user response：

This could be a solution:

given this class for the relation List

     private class Nrelation
    {
        public string T;
        public string S;
    }

This should work

    private string DOTest(List<string> match) 
    {
        List<Nrelation> testList = new List<Nrelation>(); //<--Populate your relation data here

        return testList.GroupBy(g => g.T).ToList()
          .Where(item => isEqual(item.Select(x => x.S).ToList(), match))
          .FirstOrDefault().Key;

    }

    private bool isEqual(List<string> list1, List<string> list2) 
    { 
        return Enumerable.SequenceEqual(list1.OrderBy(e => e), list2.OrderBy(e => e));
    }