query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

suarezst1984
Starting Member

16 Posts

Posted - 2009-05-15 : 13:45:51

I have something like the following tables, I am trying to build a query that basically gives me as a result the difference between each ProdID and TprodID product. I am not really sure how to make this query that will return that. Any help would be appreciated.

This first table is called aliasprodid and contains the following columns

TprodID ProdID
0230 ASCS12
1231 BMSAQ1
234J 123578

The second table is called myprodid and looks something like this

PRODID Price
ASCS12 22.5
BMSAQ1 12.3
123578 01.3

The third table is called feedid and looks like this

TprodID Tprice
0230 15.2
1231 2.5
234J 0.7

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-15 : 13:54:40

[code]DECLARE @TProd TABLE (TprodID VARCHAR(4), ProdID VARCHAR(6))
INSERT @TProd
SELECT '0230', 'ASCS12'
UNION ALL SELECT '1231', 'BMSAQ1'
UNION ALL SELECT '234J', '123578'

DECLARE @myprodid TABLE(PRODID VARCHAR(6), Price MONEY)
INSERT @myprodid
SELECT 'ASCS12', $22.5
UNION ALL SELECT 'BMSAQ1', $12.3
UNION ALL SELECT '123578', $01.3

DECLARE @feedid TABLE (TprodID VARCHAR(4), Tprice MONEY)
INSERT @feedid
SELECT '0230', $15.2
UNION ALL SELECT '1231', $2.5
UNION ALL SELECT '234J', $0.7

SELECT
T.TprodID,
T.ProdID,
M.Price - F.Tprice AS Diff
FROM
@TProd AS T
INNER JOIN
@MyProdID AS M
ON T.ProdID = M.ProdID
INNER JOIN
@FeedID AS F
ON T.TProdID = F.TProdID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources