[thelist] SQL record filtering

[dn at dittodesign.co.uk]

> - user answers question set
> - user arrives at one final 'Master Category'  (e.g. fascias)
> - you find all related 'Category's  (e.g. genuine, football)
> - you find one example of each to display

> Is that correct?

yeah thats right. You followed my explanation to a tee.

> Does Access support DISTINCTROW?

A quick google says it does.
Looks promising, although boolean value setting is a bit beyond me
as would be introducing a Master Category - Category table.

But you've given me a few avenues to explore and brought me a step
closer to a solution.

thanks for taking the time Karen.

regards,

Darren Yates
dn at dittodesign.co.uk
http://dittodesign.co.uk




----- Original Message -----
From: "Karen J. Bowen" <lists at miinx.com.au>
To: <thelist at lists.evolt.org>
Sent: Wednesday, September 11, 2002 5:17 PM
Subject: Re: [thelist] SQL record filtering


> dn at dittodesign.co.uk wrote:
> > There is only one table used in the database hopefully making
> > it very simple.
>
> unfortunately not so in this case!
>
> > It hasn't been normalised in any way. The relevant Fields
> > include
> >
> > phone model, phone make, Master category, category
> > and ID
>
> More clarification needed...
>    1. Of the above fields, which is the "product type" you mentioned
> previously?  (I'm thinking it's 'Category'?)
>    2. Is it 'category' or a 'Master category' that is determined by the
> user questions?  (I'm thinking 'Master Category'?)
>
> So, again if my understanding is correct...
>
> - user answers question set
> - user arrives at one final 'Master Category'  (e.g. fascias)
> - you find all related 'Category's  (e.g. genuine, football)
> - you find one example of each to display
>
> Is that correct?
>
> If so...
>
> this would be much, much easier if you had another table in the database
> holding all the possible 'Categories' with their matching 'Master
> Category'.  (seriously)
>
>
> In that case, one way you could go is:
>
> Query that Categories table to find all the matching 'Categories' for
> the chosen 'Master Category', then use your server-side scripting
> language of choice to set up an array of boolean values, all equal to
> False.  (indicating 'still need a match for that category')
>
> Then go back to your Products table and find a record matching each
> 'Category' and 'Master Category' combination, changing the respective
> boolean value in the array to True (to show you've got that one).
>
>
> However...  Assuming instead that such a Categories table doesn't exist,
> you need to replicate this logic, but within the one table.  I'm not
> sure on the first bit - getting the matching Categories...  possibly try
> the following, at least as a starting point:
>
>    SELECT DISTINCTROW Category
>    FROM theBigTable
>    WHERE MasterCategory = yourFinalMasterCategory
>
> For the second bit, something like the following should work:
>
>    Loop through the Categories and for each:
>    SELECT DISTINCTROW ProductID
>    FROM theBigTable
>    WHERE MasterCategory = yourFinalMasterCategory
>    AND Category = Category(i)
>
> (not checked, definitely syntactically very wrong)
>
> Does Access support DISTINCTROW?  I'm not sure.  You could also try
> finding the Maximum (or Minimum) ProductID that fits the match (to
> ensure a unique value).
>
>
> There are actually so many ways to do this, undoubtedly many better ways
> and probably also a solution just using SQL...  but I'll leave that to
> the SQL gurus, of which I'm definitely not one.  :)
>
> HTH
> Karen
> -------
> Miinx Design & Development
> e :: karen at miinx.com.au
> p :: 0413.880.302
> w :: www.miinx.com.au
>
>
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of thelist go to:
> http://lists.evolt.org Workers of the Web, evolt !