Please see: MY TEST FORM ),
There is a list of resources: each one can have many classifications and many Target Audiences. So the Classifications and Target Audiences are in their own tables, which are each cross referenced in separate XRef tables with the ResourceID from "Resources".
This is to be a search form which will return a set of Resources filtered by any one or all of the selectors: Classifications, Target Audience and Keyword search.
I need all three selectors to be active: if any one of them is selected, it should filter the results, but if not selected, it should not affect anything. I want the search box to return any record which has the keyword in either the Resources.Description field or the Resources.ResourceTitle field.
Currently only the Classification selector is active: I have just created part of the query that I really need to keep things simple, because I can't even get one selector to draw the right results: selecting any classification returns the correct stuff, but returns it too many times - I only want one of each. (Try selecting Cardiology in the Classifications selector on the left, and hit submit - you'll see that Cardiac Arrest comes back 12 times!)
I have been experimenting with table joins, but just can't seem to write the query so I get the exact results I need.
Here is my query so far:
SELECT Resources.ResourceTitle, Resources.ResourceID, Classifications.ClassificationName
FROM Resources, Classifications, ClassificationXRef
WHERE Resources.ResourceID = ClassificationXRef.ResourceID AND ClassificationXRef.ClassificationID = 'varClassification'
Here are the variable names I will be using:
varClassification, varTargetAudience, and varSearchBox are the values returned by the 3 form elements
Here is my database structure:
Classifications (this table builds the Classifications selector in the form)
ClassificationID
ClassificationName
_________________
ClassificationXRef
ClassificationID (relation: Classifications -> ClassificationID)_
ResourceID ( relation: Resources -> ResourceId)
________________
TargetAudience (This is the source of the Target Audience selector in the form..)
TargetAudienceId
TargetAudienceName
_________________
TargetAudienceXRef
ResourceID (relation: Resources -> ResourceId_)
TargAudienceID (relation TargetAudience -> TargetAudienceId)_
________________
Populations
Population
_________________
Resources (This is the source of the results list that appears when you hit submit.)
ResourceId
ResourceTitle
Creator
ContentOwner
Contact
ContactEmail
Description
URL
Population (relation: Populations ->Population)
evaluation
components
________________
_
I experimented with using the JOIN type syntax (after consulting a book) but got duplicate results with that, so I went back to simply trying WHERE/AND which I understand a little better. I just can't seem to relate all three items correctly, and remove duplicate results.
I have really tried pretty hard on this...
Oh, one more thing: Upon hitting submit, it would be great if whatever is selected could remain selected. I have no idea how to do this, since I need the initial selection to be "%"
Thanks for any help anyone can give!
Ellen
