I have two tables, one with some user configurations (#USERCONFIG) and the other (#COMBINATIONS), multiples combinations of configurations I need to find in the first table.
CREATE TABLE #COMBINATIONS (INDEX1 INT, MENU CHAR(10))
CREATE TABLE #USERCONFIG (USERID VARCHAR(10), MENU VARCHAR(10))
INSERT INTO #COMBINATIONS VALUES (1, 'ABC300')
INSERT INTO #COMBINATIONS VALUES (1, 'ABC400')
INSERT INTO #COMBINATIONS VALUES (2, 'ABC100')
INSERT INTO #COMBINATIONS VALUES (2, 'ABC500')
INSERT INTO #COMBINATIONS VALUES (2, 'ABC600')
INSERT INTO #USERCONFIG VALUES ('SMITHJ', 'ABC100')
INSERT INTO #USERCONFIG VALUES ('SMITHJ', 'ABC500')
INSERT INTO #USERCONFIG VALUES ('SMITHJ', 'ABC600')
INSERT INTO #USERCONFIG VALUES ('SMITHC', 'ABC100')
INSERT INTO #USERCONFIG VALUES ('SMITHC', 'ABC500')
INSERT INTO #USERCONFIG VALUES ('SMITHA', 'ABC100')
INSERT INTO #USERCONFIG VALUES ('SMITHA', 'ABC200')
INSERT INTO #USERCONFIG VALUES ('SMITHA', 'ABC300')
INSERT INTO #USERCONFIG VALUES ('SMITHA', 'ABC400')
INSERT INTO #USERCONFIG VALUES ('SMITHA', 'ABC600')
With this example data, I want the resultset to look like this:
'SMITHJ', '2'
'SMITHA', '1'
'SMITHC', '2'
Where it will return all users that have a match of configurations from the combinations table.
Any help would be appreciated.
The following will list users and the complete combinations they have. If it helps, you can think of it as the recipe-ingredient and user-ingredient textbook problem:
SELECT alluser.USERID, index_menu.INDEX1
FROM (SELECT DISTINCT USERID FROM #USERCONFIG) AS alluser
CROSS JOIN #COMBINATIONS AS index_menu
LEFT JOIN #USERCONFIG AS user_menu ON alluser.USERID = user_menu.USERID AND index_menu.MENU = user_menu.MENU
GROUP BY alluser.USERID, index_menu.INDEX1
HAVING COUNT(index_menu.MENU) = COUNT(user_menu.MENU)