Question on correlated subquery

Question

Hello,

Would you please help me with this:

Thank you in advance,
Michael

Answer 1

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

Answer 2

Michael,

Screenshot are a bit useless; please post table design as DDL, some sample data as DML and the expected result instead.

And why do you want to use a subquery; wouldn't a simple filter enough? Like

SELECT *
FROM Inventory AS I1
WHERE I1.quantity = 50

Answer 3

Hello OlafHelper-2800,

"And why do you want to use a subquery;" - I'm just following my textbook's exersises with my own test tables.

"please post table design as DDL, some sample data as DML and the expected result instead." ...sorry, didn't quite get it... the Inventory table is just a test table:

...and the expected result is posted on the first screenshot: I expect to get only two rows with the quantity = 50.

The same test but with the MAX(quantity) works as expected:

Answer 4

The WHERE clause (50 IN ()) in the main query does not filter anything. That was why you got all rows from the Inventory table. The following query

SELECT warehouse, product, model, quantity
FROM Inventory as I1
WHERE quantity IN (
    SELECT quantity 
    FROM Inventory as I2
    WHERE I1.warehouse = I2.warehouse AND I1.quantity = 50
)
ORDER BY warehouse, product

is the same as this one:

SELECT warehouse, product, model, quantity
FROM Inventory
WHERE quantity = 50
ORDER BY warehouse, product

Answer 5

"The WHERE clause (50 IN ()) in the main query does not filter anything." - but when I change 50 to 150 (the number that exists in the quantity column) or to 500 (that does NOT exist) the output does change (and I don't know why it changes in that way for quantity = 150)

Here's another example with WHERE IN () that does work: