[thelist] SQL record filtering

Karen J. Bowen lists at miinx.com.au
Wed Sep 11 11:18:01 CDT 2002


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





More information about the thelist mailing list