TSQL: Retreiving a value where a set of values in one table match a set of value in another

Question

Setup: An order has multiple samples, with each sample having a test. A specific combination of tests is a specific testcode. How can I match a set of rows with specific values with another table of specific values to give a single result?

Table: TestCodes

ID     TestCode  Test
1        01a       A
2        01b       F
3        02a       A
4        02a       B
5        02b       A
6        02b       C
7        02c       A
8        02c       E
9        03a       A
10       03a       C
11       03a       B
12       03a       D
13       03b       A
14       03b       C
15       03b       E
16       03c       A
17       03c       B
18       03c       E
19       04        A
20       04        C
21       04        B
22       04        D
23       04        E

Table: Orders

Order Sample Test
1     1      A
1     2      B
1     3      C
1     4      D
1     5      E
2     1      A
2     2      E

I can't find a way to return

Order TestCode
1     04
2     02c

I've tried TSQL views, but can't find a way to compare a set of values in one table to a set of values in another table. Thanks!

Answer 1

SELECT o.order, t.testcode from order o join testcodes t on o.test = t.test

It will only return one per if there's actually one, though, which in this case you don't have.

So you'd have to add in a WHERE clause to, for example, limit the Samples or limit in some other fashion that defines a unique set per order.

Answer 2

I believe you need:

SELECT o.OrderId, t.TestCode 
FROM (
    SELECT o.OrderId, t.TestCode, COUNT(*) AS intCount
    FROM Orders o
         INNER JOIN TestCodes t ON t.Test = o.Test
    GROUP BY o.OrderId, t.TestCode
) AS ot
    INNER JOIN (
        SELECT OrderId, COUNT(*) AS intCount
        FROM Orders
        GROUP BY OrderId
    ) AS o ON o.OrderId = ot.OrderId
    INNER JOIN (
        SELECT TestCode, COUNT(*) AS intCount
        FROM TestCodes
        GROUP BY TestCode
    ) AS t ON ot.TestCode = t.TestCode
WHERE o.intCount = ot.intCount
    AND t.intCount = ot.intCount

This counts the number of matching test codes for each order, and ensures this number matches both the number of tests for that test code and the number of tests for that order.

It makes the assumption that (TestCode, Test) pairs are unique in TestCodes, and that (Order, Test) pairs are unique in Orders.