CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Davronbek Rahmonov
Davronbek Rahmonov

Reputation: 109

SQL Server Select with two IN clause in conditions

I'm stuck in writing a query. It's a simple query, but I have some doubts. So I want some new approaches on the task

DECLARE @ordersToSet TABLE 
                     (
                         [Id] [BIGINT] NOT NULL,
                         [AttributeId] [BIGINT] NOT NULL
                     )

SELECT * 
FROM dbo.Order_Attributes
WHERE OrderAttributeID IN (SELECT AttributeId
                           FROM @ordersToSet) 
  AND OrderID IN (SELECT Id
                  FROM @ordersToSet)

What I want: the query should return only those Order_Attributes where both OrderId and AttributeId match the respective values in temp table @ordersToSet.

Why I think the above code could lead to unexpected results:

@ordersToSet table has the following rows

    Id     | AttributeId
    -------+------------
    486455 | 12

OrderAttributes table has the following:

    OrderID | OrderAttributeID
    --------+-----------------
    486455  | 11
    635201  | 12

In this case it should NOT select any row, however I doubt it selects A ROW with values OrderId = 486455 and AttributeId = 12

Upvotes: 0

Views: 161

Answers (1)

GMB
GMB

Reputation: 222432

The query should return only those Order_Attributes where both OrderId and AttributeId match the respective values in temp table @ordersToSet.

You seem to be looking for a simple JOIN:

SELECT a.*
FROM dbo.Order_Attributes a 
INNER JOIN @ordersToSet s
    ON a.OrderAttributeID = s.AttributeId AND a.OrderID = s.Id

Upvotes: 3

Related Questions