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.
| Author |
Topic |
|
Chasmann8
Starting Member
2 Posts |
 Posted - 2015-02-19 : 12:13:03
|
| I'm very new to SQL, but I'm struggling with using a math operator in a query, and I'm not sure if I can use it the way I've tried.Imagine a list of prices of goods for two stores. Both stores sell some of the same goods and some goods that they are the exclusive seller, but I want to see where store 2 is undercutting store 1 by exactly three dollars on the same product. I've tried this:selcct i.store,store,i.product,product,i.price,price from Price_tablewhere i.store = 1 and exists (select i.store,store,i.product,product,i.price,price from Price_table where store = 2 and product = i.product and price = i.price - 3)Can I use the subraction operator to pull those prices? Thanks in advance! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-19 : 12:28:34
|
| that looks like valid syntax (other than a typo or two). Did you run it? Does it give you what you want?Note that there are other ways:select i.store,store,i.product,product,i.price,price from Price_table icross join Price_Table jwhere i.store <> j.storeand i.product = j.productand i.price = j.price - 3 |
 |
|
|
Chasmann8
Starting Member
2 Posts |
Posted - 2015-02-19 : 12:44:48
|
[quote]Originally posted by gbritton
that looks like valid syntax (other than a typo or two). Did you run it? Does it give you what you want?I'm using a very large data set, but when I ran it, it returned 70k results before I stopped it when I'm anticipating only 2 or 3k. Looking at some of the results it returned, it seems like it pulled every product with a matching product from store 2, but didn't use the subtraction operator. I'm not sure why. Good to know if can be done though. Thanks! |
|
|
|
|
|
|
|
|
Subtraction in subquery