Newbie needs help creating Select statement!

Mintyman · November 13th, 2006, 10:56 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?

Thanks!

Jeff Mason · November 13th, 2006, 11:06 AM

Homework problem?

Simply JOIN the two tables on their ProductID. That will give you one row in the resultset for each ProductID/Level combination.

Or, since your 'specification' was somewhat vague, simply SELECT the contents of the ProductProgram table, since that all by itself contains "...each product with all 5 of their individual price levels..."

Jeff Mason
Custom Apps, Inc.
[email protected]

Mintyman · November 13th, 2006, 11:18 AM

Hi Jeff,

Thanks for the quick response.

I have tried the following but it returns the same price 5 times and I don't know how to make it return the 5 different price levels:

SELECT sysdba.PRODUCT.NAME, sysdba.PRODUCT.DESCRIPTION, sysdba.PRODUCTPROGRAM.PRICE AS Level1,
sysdba.PRODUCTPROGRAM.PRICE AS Level2, sysdba.PRODUCTPROGRAM.PRICE AS Level3, sysdba.PRODUCTPROGRAM.PRICE AS Level4,
sysdba.PRODUCTPROGRAM.PRICE AS Level5
FROM sysdba.PRODUCT INNER JOIN sysdba.PRODUCTPROGRAM ON sysdba.PRODUCT.PRODUCTID = sysdba.PRODUCTPROGRAM.PRODUCTID

Somehow I need to make the column 'level2' return the price from the ProductProgram table where 'Level' is equal to 1 and then do the same for the other columns as well.

I hope i'm making sense!

Jeff Mason · November 13th, 2006, 12:03 PM

Do you want each price level as a separate column in the resultset?

If so, then you will can JOIN the ProductProgram table repeatedly for each required level. Something like:

Code:

SELECT Name, Description,
        PP1.Price as Level1Price,
        PP2.Price as Level2Price,
        PP3.Price as Level3Price,
        PP4.Price as Level4Price,
        PP5.Price as Level5Price
      FROM Product
INNER JOIN ProductProgram PP1
            ON Product.ProductID = PP1.ProductID AND PP1.Level = 1
INNER JOIN ProductProgram PP2
            ON Product.ProductID = PP2.ProductID AND PP2.Level = 2
INNER JOIN ProductProgram PP3
            ON Product.ProductID = PP3.ProductID AND PP3.Level = 3
INNER JOIN ProductProgram PP4
            ON Product.ProductID = PP4.ProductID AND PP4.Level = 4
INNER JOIN ProductProgram PP5
            ON Product.ProductID = PP5.ProductID AND PP5.Level = 5

This could also be done via correlated subqueries:

Code:

SELECT Name, Description,
        (SELECT ProductPrograms.Price FROM ProductPrograms
            WHERE ProductPrograms.ProductID = Products.ProductID
             AND Level = 1) as Level1Price,

... (similarly for the other 4 level values)

 FROM Product
...

It's not clear to me which is a better solution. Indeed, each may result in the same execution plan.

However, note also that if there are missing level values things become a bit more complex with the INNER JOIN solution. An OUTER JOIN will be required in that case, and that is left as an exercise to the reader...

Jeff Mason
Custom Apps, Inc.
[email protected]

Mintyman · November 13th, 2006, 01:11 PM

Hi Jeff,

That seems to have done the trick :o)

I needed to show each price level in a different column and your code helped me get there.

Thank you very much!

Mintyman