question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked MikhailFirsov-1277 commented

Question on correlated subquery

Hello,

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

Thank you in advance,
Michael


sql-server-transact-sql
07-correlated4.png (176.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered MikhailFirsov-1277 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

To further Tom's response - the difference between your query and the original is the original is using MAX to get the maximum quantity from the warehouse and only returning those that have that maximum value (assuming an equal or IN is being used). If you changed the original to be: quantity < (SELECT MAX...) you would then get all rows less than the max quantity.

0 Votes 0 ·

Yes, you are right! Thank you!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered

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:
81447-08.png

...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:
81440-05.png



08.png (9.7 KiB)
05.png (152.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered GuoxiongYuan-7218 commented

"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)

81621-14.png

Here's another example with WHERE IN () that does work:
81567-15.png



14.png (161.5 KiB)
15.png (122.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would write the query like this. It is easier to understand the logic.

 SELECT d.[Name] AS [Department Name], d.DepartmentID
 FROM Department AS d 
 INNER JOIN Employee AS e ON e.DepartmentID = d.DepartmentID
 WHERE e.LastName = 'Gee';
0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.