SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 comparing data from a column with itself
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

isingmodel
Starting Member

6 Posts

Posted - 10/14/2010 :  14:59:24  Show Profile  Reply with Quote
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

Saudi Arabia
264 Posts

Posted - 10/14/2010 :  18:31:36  Show Profile  Reply with Quote
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)
Go to Top of Page

isingmodel
Starting Member

6 Posts

Posted - 10/14/2010 :  20:57:45  Show Profile  Reply with Quote
Worked beautifully. Thanks a lot
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000