comparing data from a column with itself

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

comparing data from a column with itself

Author

Topic

isingmodel
Starting Member

6 Posts

Posted - 2010-10-14 : 14:59:24

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.

Thanks

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-14 : 18:31:36

Try this:


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)

isingmodel
Starting Member

6 Posts

Posted - 2010-10-14 : 20:57:45

Worked beautifully. Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources