Member Avatar for lewilaloupe

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

  • 3 Contributors
  • 2 Replies
  • 185 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by reygcalantaol
Member Avatar for Derice

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

Member Avatar for reygcalantaol

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"

Edited by reygcalantaol because: n/a
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.