SEO

#1 (**permalink**) 03-01-2008, 11:34 AM

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?

#2 (**permalink**) 03-01-2008, 11:34 AM

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

#3 (**permalink**) 03-01-2008, 11:34 AM

Hi Mark,

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

Mintyman

<markc600@hotmail.com> wrote in message
news:1163429919.872807.120980@b28g2000cwb.googlegr oups.com...
> 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
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Newbie needing help creating query