[thelist] MySQL Query Issue

[Vail, Warren]

Haven't tested this, but it should work as long as the data relates.

Select productName, shortDesc, longDesc, classDate
>From PRODUCTS, CLASSES_SCHEDULE
Where PRODUCTS.productID = CLASSES_SCHEDULE.productID

Should produce rows where entries exist in both tables, i.e. it will NOT
produce results for a class that is not scheduled.

You may also want to restrict the schedule dates to this year or to only
future dates (if you are offering for sale) or include past dates for
reviewing against past registrations.  Lots of questions arise around the
schedule date, like how far in advance of a class are the schedules loaded,
and how far in advance do you show scheduled dates (they could be different
criterion).

If you want to see products that are not scheduled, be prepared for using an
outside join (and retrieving null dates).

HTH,

Warren Vail


> -----Original Message-----
> From: thelist-bounces at lists.evolt.org 
> [mailto:thelist-bounces at lists.evolt.org] On Behalf Of RUST Randal
> Sent: Monday, November 29, 2004 12:13 PM
> To: thelist at lists.evolt.org
> Subject: [thelist] MySQL Query Issue
> 
> 
> I have two tables:
> 
> PRODUCTS
> --------
> productID
> productName
> shortDesc
> longDesc
> itemType
> 
> CLASSES_SCHEDULE
> ----------------
> scheduleID
> productID
> classDate
> 
> Simply put, we are scheduling educational classes here. Only 
> each class is also a product that can be purchased by a 
> customer. What I want to do is show the use all of the 
> information regarding the class (pulled from the products 
> table), and list all of the available class dates. Like so:
> 
> Class Name
> Short Description
> Long Description
> Class Dates
> 
> Getting everything but the Class Dates is a snap. I just 
> can't figure out how to return the multiple dates using just 
> SQL. I could always load each of the productIDs into an array 
> and then loop through them with PHP, but that seems kind of excessive.
> 
> TIA.
> 
> ----------
> Randal Rust
> Covansys Corporation
> Columbus, OH 
> 
> Confidentiality Statement:
> 
> This message is intended only for the individual or entity to 
> which it is addressed. It may contain privileged, 
> confidential information which is exempt from disclosure 
> under applicable laws. If you are not the intended recipient, 
> please note that you are strictly prohibited from 
> disseminating or distributing this information (other than to 
> the intended recipient) or copying this information. If you 
> have received this communication in error, please notify us 
> immediately by return email.
> 
> -- 
> 
> * * Please support the community that supports you.  * * 
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>