I have a table with the following columns (ID, category,attributes, price)
what I want to do is issue a SQL statement that will return all the rows in the table where the price is within some value (say 3 dollars) of a category. So what I want to do is something like this
Select * FROM table where ((SELECT price FROM table WERE category='Socks') - price ) < 3 AND ((SELECT price FROM table WERE category='Socks') - price ) > -3
The problem I am running into is that the SubQuery can return many values. I want to be able to extract everything from the table that has a price of +/- $3 of any of the socks (or belts or jeans etc) in the table.
Any thoughts? all the data is in the table and if I use the IN keyword I can make the statement return items that are exactly the same price, but I want to be able to select items that are around those values but not quite.
SELECT *
FROM table AS T1
WHERE NOT EXISTS(SELECT *
FROM table AS T2
WHERE T2.category = 'Socks'
AND T2.price < T1.price - 3)
AND NOT EXISTS(SELECT *
FROM table AS T2
WHERE T2.category = 'Socks'
AND T2.price > T1.price + 3)
comparing data from a column with itself
Posted - 10/14/2010 : 14:59:24
