1

I have a UDT that receive 2 parameters which is ID and Quality

CREATE TYPE [dbo].[ItemUdt] AS TABLE(
[ID] [int] NOT NULL,
[Quality] [int] NULL
)

This UDT will be use as condition to filter records from a table

@ItemList AS ItemUdt READONLY

What i trying to do is get the records that match both ID and Quality

SELECT * FROM tbl_Item 
WHERE [ID] IN (SELECT ID FROM @ItemList)
AND [Quality] IN (SELECT Quality FROM @ItemList)

However, above query will match all listed ID that consists of all Quality from the UDT. For example, if the UDT consists of (1001, 'Good'),(1002, 'Bad'), the result will display all the ID with 1001 and 1002 together with Quality of 'Good' and 'Bad'. But my expected result is as below:

SELECT * FROM tbl_Item 
WHERE ([ID] = '1001' AND [Quality] = 'Good')
OR ([ID] = '1002' AND [Quality] = 'Bad')

Is there any way I can write a query that match above format?

7

Some DBMSs allow tuples to be used in IN.

WHERE  (ID, Quality ) IN (SELECT ID, Quality FROM @ItemList) 

SQL Server isn't one of then and just allows a single column.

You can use WHERE EXISTS

SELECT *
FROM   tbl_Item tI
WHERE  EXISTS (SELECT *
               FROM   @ItemList I
               WHERE  I.ID = tI.ID
                      AND I.Quality = tI.Quality) 
5
SELECT t.col1, t.col2, etc.
  FROM dbo.tbl_Item AS t
  INNER JOIN @ItemList AS tvp
  ON t.ID = tvp.ID
  AND t.Quality = tvp.Quality;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.