Nested SELECT statements

[pezza]

Hi,

I have managed to get a nested query to work partly, but i need to somehow add another nest, which i cannot get to work.

To explain more, i wanted to firstly find records that did not exist in another table, to which i used the following

SELECT products.idProduct, products.sku FROM products WHERE NOT EXISTS
(SELECT optionsinventory.idProduct, optionsinventory.inventory FROM optionsInventory WHERE products.idProduct = optionsinventory.idProduct AND optionsinventory.inventory <> 0)
ORDER BY idProduct

This correctly tells me any products that do not have a zero option inventory.

What i want to do now is to display these from the options inventory table, so that it shows me ALL records in options inventory that match the returned idProducts from the above query, but i cannot get this to return the correct results, it is returning id's that are NOT returned in the original results.

I have tried doing this

SELECT optionsInventory.idProduct FROM optionsInventory WHERE EXISTS (SELECT products.idProduct, products.sku FROM products WHERE NOT EXISTS
(SELECT optionsinventory.idProduct, optionsinventory.inventory FROM optionsInventory WHERE products.idProduct = optionsinventory.idProduct AND optionsinventory.inventory <> 0))
ORDER BY idProduct

If i try to use the "As" function i get errors and i think this may be because i am using a web based SQL tool called myLittleAdmin, that may not be able to declare.

Where am I going wrong?

Ultimately, i am trying to get rid of all entries in the optionsInventory table where a value of 0 does not exist for the product id. The problem is the table structure looks like:

idOptInv-------idProduct-------Options-------Inventory
1----------------27----------------10,36----------90
2----------------27----------------12,56----------120
3----------------27----------------84,31----------0
4----------------35----------------20,40----------99
5----------------35----------------19,40----------99
6----------------35----------------25,40----------120

In the above example, 35 would be returned by my query as a product that does not contain any 0 entered inventory, which is correct. I now want to end up returning data from this table, which i would expect to be records 4,5 and 6, so that i can then perform a DELETE query the same to delete these.

I would really appreciate any help.

Thanks

Andrew

[urtrivedi]

following query will give ids of product which have invetory column's value as 0 select distinct idproduct where inventory=0 from optionsInventory follwing query deletes all product records which do not have invertory column value as 0

delete from optionsInventory where idproduct not in (
 select distinct idproduct where inventory=0 from optionsInventory)

[pezza]

Hi

Thanks for the reply. Neither of those worked, i had to move the from clause around to correct the syntax, however when it did run, this is just pulling out every record that is not 0. I don't want this, as i need to keep all entries where a product contains a zero inventory.

So in this query, it would delete 1,2,4,5 and 6. I only want to delete 4,5 and 6 as idproduct 27 has an entry with a zero inventory.

My first query gave me the product id's where there was NO zero entry atall for that idproduct, which is correct. i just now want to do the same but sourcing from the optionsinventory table only the unique idproducts greater than 0, BUT that does not also contain an inventory record of 0

Thanks

Andrew

[pezza]

I stand corrected...

I obviously was clouding my view as my first query didn't return the correct results.

Anyway, after hours of double checking, and checking sample returned records and backing my db up to 3 locations! :) I amended from SELECT to DELETE.

It worked! I have checked a few records and it seems to have done the trick. I did have to move the from clause though to before the where clause, but other than that it was spot on.

Thank you, and apologies for doubting you :$

I guess as usual, i was trying to overcomplicate things!

Andrew