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_table
where 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 i
cross join Price_Table j
where i.store <> j.store
and i.product = j.product
and 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