sql server (alternate): SQL Question -- sql server (alternate)

SQL Question

shane.peterson NO[at]SPAM figis.com
1/30/2004 6:51:23 AM

sql server (alternate): I have a one to many relationship between two tables. A products
table and a prices table. The prices table has a description field
that describes the type of price (Original Retail, Final Retail) and a
price field. I want to be able to specifically query the "Original
Retail" and "Final Retail" for each product record and list each
retail price as a column. I would like the result set to look like:

Product OriginalRetail FinalRetail
100 9.99 11.99
200 7.99 9.99
222 15.99 20.99

Re: SQL Question

Simon Hayes
1/30/2004 6:57:38 PM

[quoted text, click to view]

Without table DDL and sample data, this is a guess, and may be completely
wrong. If so, then please post those details.

Simon

select
p.Product,
pr1.Price as 'OriginalRetail',
pr2.Price as 'FinalRetail'
from
dbo.Products p
inner join dbo.Prices pr1
on p.ProductID = pr1.ProductID
and pr1.Description = 'Original Retail'

inner join dbo.Prices pr2
on p.ProductID = pr2.ProductID
and pr2.Description = 'Final Retail'
where
...