WHERE IN clause with AND in T-SQL-CodePudding

I am trying to match data in T-SQL from my tables as kindof an "IN with AND" clause. I can't think of how to describe this exactly other than with an example...

Here are my tables of data...

tbl_Values

pkId	nv_Name	nv_Values
1	test1	1.1,2.1,3.1
2	test2	1.2,2.1,3.1
3	test3	1.1,2.2,3.1

tbl_MatchValues

pkId	nv_MatchValue
1	1.1
2	1.2
3	1.3
4	2.1
5	2.2
6	2.3
7	3.1
8	3.2
9	3.3

tbl_Values_MatchValues

pkId	fk_ValueId	fk_MatchValueId
1	1	1
2	1	4
3	1	7
4	2	2
5	2	4
6	2	7
7	3	1
8	3	5
9	3	7

In the front-end I have a checkbox selector that allows the user to pick certain Values that are then passed to MSSQL in a CSV format, which populates the variable @CSV

-- CSV VALUES ARE FROM PICKLIST IN FRONT-END CODE
DECLARE @CSV AS VARCHAR(MAX) = '1.1,3.1'

-- CREATE TEMP TABLE TO HOLD CSV DATA
CREATE TABLE #TblValues (Number VARCHAR(MAX))
IF @CSV IS NOT NULL
INSERT INTO #TblValues
SELECT * FROM SplitString(@CSV,',')

SELECT val.[nv_Name]
  ,val.[nv_Values]
  ,mv.nv_MatchValue -- JUST FOR DEMONSTRATION
FROM [tbl_Values] AS val
JOIN [tbl_Values_MatchValues] AS vmv ON vmv.fk_ValueId = val.pkId
JOIN [tbl_MatchValues] AS mv ON vmv.fk_MatchValueId = mv.pkId
WHERE mv.nv_MatchValue IN (SELECT * FROM #TblValues)

The goal here is to get a list of all rows in tbl_Values that have ALL the Values listed in @CSV, but what I get is more of an OR so I get all the rows that have ANY of the Values listed, such as...

nv_Name	nv_Values	nv_MatchValue
test1	1.1,2.1,3.1	1.1
test1	1.1,2.1,3.1	3.1
test2	1.2,2.1,3.1	3.1
test3	1.1,2.2,3.1	1.1
test3	1.1,2.2,3.1	3.1

And here, I would like to get test1 and test3 but NOT test2.

Any help is greatly appreciated...

[UPDATE]

I edited the query to read like this...

-- CSV VALUES ARE FROM PICKLIST IN FRONT-END CODE
DECLARE @CSV AS VARCHAR(MAX) = '1.1,3.1'

-- CREATE TEMP TABLE TO HOLD CSV DATA
CREATE TABLE #TblValues (Number VARCHAR(MAX))
    IF @CSV IS NOT NULL
    INSERT INTO #TblValues
    SELECT * FROM SplitString(@CSV,',')

SELECT val.[nv_Name]
      ,val.[nv_Values]
      --,mv.nv_MatchValue
  FROM [tbl_Values] AS val
  JOIN [tbl_Values_MatchValues] AS vmv ON vmv.fk_ValueId = val.pkId
  JOIN [tbl_MatchValues] AS mv ON vmv.fk_MatchValueId = mv.pkId
  WHERE mv.nv_MatchValue IN (SELECT * FROM #TblValues)
  AND NOT EXISTS(
    SELECT * FROM [tbl_MatchValues] t 
    LEFT JOIN #TblValues ON #TblValues.[Number] = t.nv_MatchValue
    WHERE t.pkId = vmv.fk_MatchValueId
    AND #TblValues.[Number] IS NULL)

And I am still getting test2 in my results...

CodePudding user response：

To check that there are not match-values for the given vmv.fk_MatchValueId that don't exist in the #TblValues table, you could add:

AND NOT EXISTS (
SELECT * FROM [tbl_MatchValues] t 
LEFT JOIN #TblValues ON #TblValues.[Number] = t.nv_MatchValue
WHERE t.pkId = vmv.fk_MatchValueId
AND #TblValues.[Number] IS NULL)

If you like it better, you could replace the "LEFT JOIN ..." and "IS NULL" check for a "NOT IN (...)". Didn't have time to create the tables and acutally test the query for real, but the principal would work. If there are large amounts of data, you might want to consider a different model since the "not exists" may prove time consuming in those cases.

CodePudding user response：

The following ROW_NUMBER() approach works on the limited sample set. If your data or match criteria is more complex than shown your results may vary.

DECLARE @t_val TABLE (pkId INT, nv_Name VARCHAR(10), nv_Values VARCHAR(30))

INSERT INTO @t_val
VALUES
(1, 'test1', '1.1,2.1,3.1'),
(2, 'test2', '1.2,2.1,3.1'),
(3, 'test3', '1.1,2.2,3.1')

DECLARE @t_Match TABLE (pkId INT, nv_MatchValue VARCHAR(30))

INSERT INTO @t_Match
VALUES
(1, '1.1'),
(2, '1.2'),
(3, '1.3'),
(4, '2.1'),
(5, '2.2'),
(6, '2.3'),
(7, '3.1'),
(8, '3.2'),
(9, '3.3')

DECLARE @t_val_Match TABLE (pkId INT, fk_t_val INT, fk_t_Match INT)

INSERT INTO @t_val_Match
VALUES
(1, 1,  1),
(2, 1,  4),
(3, 1,  7),
(4, 2,  2),
(5, 2,  4),
(6, 2,  7),
(7, 3,  1),
(8, 3,  5),
(9, 3,  7)

DECLARE @CSV TABLE (Number VARCHAR(MAX))

INSERT INTO @CSV
VALUES
('1.1'),
('3.1')

;WITH CTE
AS
(
SELECT val.[nv_Name]
  ,val.[nv_Values]
  ,mv.nv_MatchValue -- JUST FOR DEMONSTRATION
  ,ROW_NUMBER() OVER(PARTITION BY val.[nv_Name] ORDER BY val.[nv_Name]) AS NumMatches
FROM @t_val AS val
JOIN @t_val_Match AS vmv ON vmv.fk_t_val = val.pkId
JOIN @t_Match AS mv ON vmv.fk_t_Match = mv.pkId
WHERE mv.nv_MatchValue IN (SELECT Number FROM @CSV)
)

SELECT c.nv_Name
    , c.nv_Values 
FROM CTE c
GROUP BY nv_Name, nv_Values
HAVING MAX(NumMatches) = (SELECT COUNT(*) FROM @CSV)

nv_Name	nv_Values
test1	1.1,2.1,3.1
test3	1.1,2.2,3.1