Hi,
This is a SQL statement query (so may be in the wrong forum). Any help much appreciated!

I have 3 tables in an Access DB (I know!):

IndexData is main table with title, description, date, fileid
Category table has categories (Cat1) associated to fileid in IndexData i.e. a fileid could be listed 5 times(rows) with different categories associated to it.
Subcat table has category (Cat2) associated to fileid in IndexData

I can do a search for a search term (strtxt) within a specific category (strcat1) and get correct results using:

strcat1=request.querystring("cat1")
strtxt=request.querystring("searchtext")

SELECT DISTINCT id.[Title],id.[Description],id.[LastModified], c1.[Cat1], c2.[Cat2] FROM IndexData id, Category c1, Subcat c2 WHERE c1.[FileID]=id.[FileID] AND c2.[FileID]=id.[FileID] AND c1.[Cat1] = '" & strcat1& "' AND id.[Title] LIKE '%" & strtxt &"%'"

I then loop through the results to display them.

However, when I try and see results for a search term in all Categories, in my results I see Title, Description, LastModified repeated for each category. I can't get those fields to show uniquely (I would then have a list of Categories listed next to each row).

I'm thinking it must be possible to put a sub-query within the main SELECT statement, but cannot work out how.

I hope that makes sense and someone can advise me please.

Thanks
lewilaloupe

Recommended Answers

All 2 Replies

Can you show us the result you produced and expected result?

Group the result by adding GROUP BY clause after the WHERE clause.

SELECT DISTINCT id.[Title],id.[Description],id.[LastModified], c1.[Cat1], c2.[Cat2] FROM IndexData id, Category c1, Subcat c2 WHERE c1.[FileID]=id.[FileID] AND c2.[FileID]=id.[FileID] AND c1.[Cat1] = '" & strcat1& "' AND id.[Title] LIKE '%" & strtxt &"%' GROUP BY id.Title,id.description,id.lastmodified,c1.cat1.c2.cat3"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.