WHERE IN clause with AND in T-SQL

gotmike 0
Viewed 91

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...

[UPDATE]

Okay, I think I have it. At least I get the results I want, however it may not be very performant.

Here is the SQL...

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

-- 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 
        tvmv.fk_MatchValueId
        ,tmv.nv_MatchValue
        ,tvmv.fk_ValueId
        ,tv.nv_Name
        ,tv.nv_Values
    INTO #TblCounts
    FROM tbl_Values_MatchValues AS tvmv
    JOIN tbl_MatchValues AS tmv ON tvmv.fk_MatchValueId = tmv.pkId
    JOIN tbl_Values AS tv ON tvmv.fk_ValueId = tv.pkId
    WHERE fk_MatchValueId IN (SELECT * FROM #TblValues)

SELECT 
        tc.nv_Name
        ,tc.nv_Values
        ,tc.nv_MatchValue
    FROM #TblCounts AS tc
    WHERE (SELECT COUNT(*) FROM #TblCounts AS tcinner WHERE tc.fk_ValueId = tcinner.fk_ValueId) = (SELECT COUNT(*) FROM #TblValues)

And here is the result... |nv_Name|nv_Values|nv_MatchValue| |-|-|-| |test1|1.1,2.1,3.1|1.1| |test1|1.1,2.1,3.1|3.1| |test3|1.1,2.2,3.1|1.1| |test3|1.1,2.2,3.1|3.1|

Is this bad practice or going to be slow at scale?

tsql sql-server sql
2 Answers

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.

Devosaur0

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.

EDIT: Added a CROSS JOIN with the CSV values to get them in the final SELECT as desired.

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 
    , v.Number
FROM CTE c
CROSS JOIN @CSV v
GROUP BY nv_Name, nv_Values, v.Number
HAVING MAX(NumMatches) = (SELECT COUNT(*) FROM @CSV)
nv_Name nv_Values Number
test1 1.1,2.1,3.1 1.1
test1 1.1,2.1,3.1 3.1
test3 1.1,2.2,3.1 1.1
test3 1.1,2.2,3.1 3.1
dogyog0
Related