Search Page

[wingnut]

I am constructing a search page using asp and querying 2 tables in an Access database. Table 1 is a product list and table 2 is a list of features each product has. Therefore you may have multiple entries in table 2 with the same productID number from table 1. I need to search on fields in table 1 and 2 but obviously table 2 will create duplicate results. If someone can describe the approach to solving an issue like this I would be grateful.

TIA

[mrMister]

Have a read up on SQL JOIN:

[w3schools.com...]

[wingnut]

I understand Join but I cannot overcome the problem where table 2 will produce duplcate records.

For instance if someone searches on products with mulitple features, this could product 2 or more duplcate records. I just need to be able return a distinct record from the search, if this makes sense?

[mrMister]

It sounds like you want to creat a dervied table (or temoprary table) using your join and then use a select distinct on that dervied/temporary table.

However, I'm not overly farmiliar with Access's support for dervied and temporary tables.

[wingnut]

Thanks for reply. I think Access does support Temporary Tables. I will look into this.

[aspdaddy]

You dont neeed temp tables, you just need to use select DISTINCT and have 3 tables not 2.

I have this scanario on many sites and the search works fine.

Products (ProductID, Color,Size)
Features (FeatureID, FeatureName)
ProductFeatures(ProductID,FeatureID)

Search Query/View :

Create View Search as
SELECT Products.ProductID, Products.Color, Products.Size, Features.FeatureName
FROM Products INNER JOIN (Features INNER JOIN ProductFeatures ON Features.FeatureID = ProductFeatures.FeatureID) ON Products.ProductID = ProductFeatures.ProductID

SELECT Distinct Search.ProductID
FROM Search
where FeatureName='Stripe' or FeatureName='Spot'

Returns no duplicates even if products have both spots and stripes.

[wingnut]

Thx aspdaddy I'll give your suggestion a go.