[ic] Ideas to substitute for a [query] inside a [query]?

[cfm@maine.com]

On Wed, Feb 14, 2001 at 09:33:19PM -0800, Dan B wrote:
> Thanks for the reply Christopher F. Miller,
> 
> At 12:07 AM 2/15/2001 -0500, you wrote:
> >On Wed, Feb 14, 2001 at 08:15:15PM -0800, Dan B wrote:
> > > Since Interchange cannot do a [query] inside a [query] (docs say "Nesting:
> > > NO"), what can I use to get the same functionality?
> >
> >There are **at least** four things you can do:
> >1) Figure out how to rewrite the query as a JOIN
> 
> I would really love to do this, but I didn't think a JOIN would apply to my 
> situation.  Here's psuedocode for what I'm trying to do:
> 
> SELECT category WHERE x = y
>          display category1
>                  SELECT skus WHERE x = category1 AND x = y
>                          display sku1
>                          display sku2
>                          display sku3
>                          display sku4
>          display category2
>                  SELECT skus WHERE x = category2 AND x = y
>                          display sku1
>                                  display sku2
>                          display sku3
>                          display sku4
>          display category3
>                  SELECT skus WHERE x = category3 AND x = y
>                          display sku1
>                          display sku2
>                          display sku3
>                          display sku4
> 
> Is there anyway to get the above result from one SQL query?


SELECT category,skus WHERE x=y ORDER BY category;
  while (@return) {
   display_category if (new_category);
   display_item
  }

Maybe they are in different tables?
SELECT categories.category,products.skus WHERE products.category=categories.category AND x=y ORDER BY categories.category;
That will work the first time and then bite you big time when you
want to put an item into n categories.  That wants a three table
join (at least) where you keep the 1:n relation of product to category
separate from products table.

   


-- 

Christopher F. Miller, Publisher                             cfm@maine.com
MaineStreet Communications, Inc         208 Portland Road, Gray, ME  04039
1.207.657.5078                                       http://www.maine.com/
Content management, electronic commerce, internet integration, Debian linux