question

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

Question on contained queries 2

Hello,

While recaping the chapter on contained queries I decided to modify the quiery which I had already asked about eariler ...

 SELECT warehouse, product, model, quantity
 FROM Inventory as I1
 WHERE quantity IN  
 (select quantity
 From Inventory as I2
 where I1.warehouse=I2.warehouse)
 Order by warehouse, product

...to further restrict the resulting data set from the inner quiry, for example:

 SELECT warehouse, product, model, quantity
 FROM Inventory as I1
 WHERE quantity IN  
 (select quantity
 From Inventory as I2
 where I1.warehouse=I2.warehouse **and i2.model = 'X'**)
 Order by warehouse, product

I was expecting that in this case the number of quantity values in the WHERE quantity IN predicate would be less than without the extra "and i2.model = 'X'" condition.

The result was exactly that I was expecting:
92112-q1.png

Another condition "and i2.product = 'Samsung'" also returned the correct result (at least as I think, of course):
92113-q0.png

But when I changed the "and i2.product = 'Samsung'" to "and i2.product = 'iPhone'" I got the result that I can't perceive: as far as I understand the resulting 'quantity' set should contain ALL possible values because the iPhone model has all available values of quantity column, but nevertherless the two rows - 5th and 9th - are missing (in spite of the fact that corresponding values of their 'quantity' column fall into 10, 50 ..., 200 set).
92114-q2.png

Why?

Thank you in advance,
Michael

sql-server-transact-sql
q1.png (47.2 KiB)
q0.png (54.2 KiB)
q2.png (59.4 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.

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

Why?

Because your queries don't make much sense.

In the sub query you join on warehouse and filter on iPhone.
In warehouse San Fransico there is no iPhone with quantity of 100 => Samsung Note 8 with quantity 100 isn't returned
In warehouse San Jose there is no iPhone with quantity of 200 => Samsung Galaxy with quantity 200 isn't returned.



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

OlafHelper-2800, thank you very much for the explanation!!!

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.