[thelist] MySQL Query Issue

Joshua Olson joshua at waetech.com
Mon Nov 29 15:07:36 CST 2004


> -----Original Message-----
> From: RUST Randal
> Sent: Monday, November 29, 2004 3:13 PM
> 
> 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

Randal,

Generally, your easiest method is to simply perform the join and return all
rows with the information from the products table repeated in each of the
detail rows.  Then, in your display routine use the PK to differentiate
between class boundaries as you loop through the resulting set.

If you are concerned about the amount of data returned, you could also do
something like this (assuming for a moment that MySQL definitely supports
it)

SELECT
    productID
  , productName
  , shortDesc
  , longDesc
  , NULL AS classDate
FROM products
UNION ALL
SELECT
    productID
  , NULL
  , NULL
  , NULL
  , classDate
FROM classes_schedule
INNER JOIN product
ON product.productID = classes_schedule.productID
ORDER BY product_id. classDate

You will use the same display routine as outlined above, but now only the
first row of each product includes detail.  The rest do not at all.  In case
you are wondering why I did an INNER JOIN on the second part of that, it was
so that records for class_schedule are returned only for actual products
that still exist in the database.  I know it's oft easy to orphan child
records.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168 




More information about the thelist mailing list