Newbie needing help creating query

Hi,

I know some SQL but not enough to write the query i'm trying to create and
could do with some help!

I have 2 tables (Product and ProductProgram) that are linked by a common
identified 'ProductID'. Each product has 5 different price levels
(1,2,3,4,5) and these are stored in the ProductProgram table.

The ProductProgram table contains the following columns:

ProductID
Level
Price

The Product table has the following columns:

ProductID
Name
Description

Can anyone show me how to return each product with all 5 of their individual
price levels?SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5|||Hi Mark,

Thanks very much! I would never have gotten there on my own!

Mintyman

<markc600@.hotmail.comwrote in message
news:1163429919.872807.120980@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5
>