SQL – Finding foreign key ID associated with a combination of multiple/variable table variable rows

In a Microsoft SQL Server stored procedure, I have table variable, @PlayerComboStrategy, with a variable number of rows, and columns PLAYER_ID, POSITION_CODE, STATUS_CODE.

I am looking for the most efficient way to retrieve the STRATEGY_ID associated with the exact combination of all of my table variable rows, if it exists. In other words, how can I find the STRATEGY_ID involving all of the PLAYER_IDs with their specific POSITION_CODE and STATUS_CODE. In the event that it does not exists, I will proceed with its creation in my stored procedure.

I was thinking of pulling a list of all of the STRATEGY_IDs for each rows matching the combination of PLAYER_ID, POSITION_CODE and STATUS_CODE and see if there is a common ID between the lists. However, this will fail if, for example, three players (and their position and status code) are associated with a strategy, and the exact same combination plus one (or many) extra players are associated with another strategy. That being said, the STRATEGY_ID retrieved must be built from x number of rows from PLAYER_STRATEGY matching the x number of rows from @PlayerComboStrategy.

An example of @PlayerComboStrategy

+-----------+---------------+-------------+
| PLAYER_ID | POSITION_CODE | STATUS_CODE |
+-----------+---------------+-------------+
|        33 |             2 |           2 |
|        23 |             4 |           1 |
+-----------+---------------+-------------+

A sample of PLAYER_STRATEGY:

+-------------------+-------------+-----------+---------------+-------------+
| PLAYER_STATEGY_ID | STRATEGY_ID | PLAYER_ID | POSITION_CODE | STATUS_CODE |
+-------------------+-------------+-----------+---------------+-------------+
|              1490 |         511 |        64 |             2 |           2 |
|              1491 |         511 |        33 |             2 |           2 |
|              1492 |         511 |        23 |             4 |           1 |
|              1493 |         512 |        33 |             2 |           2 |
|              1494 |         512 |        23 |             5 |           1 |
|              1495 |         513 |        33 |             2 |           2 |
|              1496 |         513 |        23 |             4 |           1 |
|              1497 |         514 |        33 |             2 |           2 |
+-------------------+-------------+-----------+---------------+-------------+

For this example, I would want STRATEGY_ID = 513 to be found.

Answer

Here is one way to tackle this. I included a fully working example so you can see a nice and easy way to share data. 🙂 There are multiple ways you can tackle this kind of thing but this is what came to my mind first.

declare @PlayerStrategy table
(
    PLAYER_STATEGY_ID int
    , STRATEGY_ID int
    , PLAYER_ID int
    , POSITION_CODE int
    , STATUS_CODE int
)

insert @PlayerStrategy values
(1490, 511, 64, 2, 2)
, (1491, 511, 33, 2, 2)
, (1492, 511, 23, 4, 1)
, (1493, 512, 33, 2, 2)
, (1494, 512, 23, 5, 1)
, (1495, 513, 33, 2, 2)
, (1496, 513, 23, 4, 1)
, (1497, 514, 33, 2, 2)

declare @PlayerComboStrategy table
(
    PLAYER_ID int
    , POSITION_CODE int
    , STATUS_CODE int
)

insert @PlayerComboStrategy values
(33, 2, 2)
, (23, 4, 1)


select ps.STRATEGY_ID
from @PlayerStrategy ps
join @PlayerComboStrategy pcs on ps.PLAYER_ID = pcs.PLAYER_ID
                                        and ps.POSITION_CODE = pcs.POSITION_CODE
                                        and ps.STATUS_CODE = pcs.STATUS_CODE
group by ps.STRATEGY_ID
having count(ps.PLAYER_ID) = (select count(*) from @PlayerComboStrategy)
    and count(ps.PLAYER_ID) = (select count(*) from @PlayerStrategy ps2 where ps2.STRATEGY_ID = ps.STRATEGY_ID)