Multiply only if on list - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Multiply only if on list

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-09-20 : 10:19:14

I have three tables, one named PriceList with fields catalogID, productCode and price like this:

catalogID productCode price
1 A 1
1 B 2
1 C 3
1 D 4
2 A 5
2 B 6
2 C 7
2 D 8

Another named Catalogs with fields catalogID and taxMultiplier like this

catalogID taxMultiplier
1 1.17
2 1

And one more, TaxAppliesTo with one column, productCode like this

productCode
A
C

I need now to return productCode and price from PriceList where catalogID = 1 with correct prices so the result is like this:

productCode price
A 1.17
B 2
C 3.34
D 4

I can't seem to solve this "convert and multiply"-part.

I have something like this

SELECT PriceList.catalogID, PriceList.productCode, PriceList.price * TaxAppliesTo.productCode
FROM PriceList,Catalogs,TaxAppliesTo
WHERE catalogID=1
LEFT OUTER JOIN PriceList ON PriceList.productCode = TaxAppliesTo.productCode
INNER JOIN Catalogs.catalogID = PriceList.catalogID

Do I need some kind of Case-When-Then?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-09-20 : 11:40:01

The code is mixing old style and new style FROM clause syntax, which is not allowed. Try to (inner) join the three tables using only the new style joins and see what data you have. I think you'll see that you have the data elements necessary to derive the results you want returned. Next, use the WHERE clase to eliminate the records you don't want.

HTH

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources