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:
Another condition "and i2.product = 'Samsung'" also returned the correct result (at least as I think, of course):
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).
Why?
Thank you in advance,
Michael