Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 13th, 2006, 10:56 AM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Newbie needs help creating Select statement!

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!
 
Old November 13th, 2006, 11:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
je.mason@comcast.net
 
Old November 13th, 2006, 11:18 AM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
 
Old November 13th, 2006, 12:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
je.mason@comcast.net
 
Old November 13th, 2006, 01:11 PM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Problem - Newbie jackiew SQL Server 2000 3 October 14th, 2008 07:44 PM
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Newbie in need of help With Select Statement alex350r Oracle 1 December 1st, 2005 10:50 AM
select statement collie MySQL 0 January 14th, 2004 04:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.