Question on correlated subquery

Mikhail Firsov 1,876 Reputation points
2021-03-25T11:28:02.22+00:00

Hello,

Would you please help me with this:
81490-07-correlated4.png

Thank you in advance,
Michael

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
0 comments
Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2021-03-25T15:26:40.663+00:00

    You are only matching on warehouse. So your query basically says if ANY row in that warehouse has the quantity you are looking for, return ALL the rows in that warehouse.

    To get what you want, you would have to match on warehouse, product, and model, e.g., 

    Select warehouse, product, model, quantity
From Inventory I1
Where 50 In
  (Select quantity From Inventory I2
Where I1.warehouse = I2.warehouse
And I1.product = I2.product
And I1.model = I2.model)

    Tom

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mikhail Firsov 1,876 Reputation points
    2021-03-26T09:43:27.433+00:00

    Thank you all for your explanations!

    "I would write the query like this. It is easier to understand the logic." - yes, it's easier but my goal is to learn correlated subqueris.

    "You are only matching on warehouse. So your query basically says if ANY row in that warehouse has the quantity you are looking for, return ALL the rows in that warehouse." - that's exactly I've been thinking about but failed to interpret it correctly.
    Thank you all once again!

    Regards,
    Michael

    0 comments