Need to remove duplicates from results (ASP and SQL)

[agr1]

Hi everybody, I've made an ASP based website for a "parent" type company, and the (MS Access) database it's based on includes an extensive database of companies: vendors, providers, manufacturers etc. The website has a Company Search function which makes it possible to search the db in various ways, including product categories, location, keywords etc.

My problem is, that a lot of the companies operate in multiple fields of production (ProductCategories table), and therefore the same company has multiple entries. This also shows up in the results - I can't get rid of the duplicates... I've tried to use SELECT DISTINCT but it doesn't seem to work for me... Below is the SQL Query for the Recordset from my Results.asp page:

Dim rsResults
Dim rsResults_numRows
Dim sCond

If (Len(Request.QueryString("compname")) > 0) Then
	sCond = "Companies.CompanyName Like '%"&	Request.Querystring("compname") &"%' AND"
End If	

If (Len(Request("product")) > 0) Then
	sCond = sCond & " CompanysCategories.SubCategoryID IN("& Request("product") &") AND"
End If

If (Len(Request("productcat")) > 0) Then
	sCond = sCond & " CompanysCategories.ProductCategoryID = "& Request("productcat") &" AND"
End If	

If (Len(Request("province")) > 0) Then
	sCond = sCond & " Companies.ProvinceID= "& Request("province") &" AND"
End If

If (Len(Request.QueryString("region")) > 0) Then
	sCond = sCond & " Companies.Region = "& Request.QueryString("region") &" AND"
End If

If (Len(Request.QueryString("keywords")) > 0) Then
	sCond = sCond & " Companies.ProductsServices Like '%"& Request.QueryString("keywords") &"%' AND"
End If	

sCond = Left(sCond,Len(sCond)-4) 

Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_dsnMyComp_STRING
rsResults.Source = "SELECT DISTINCT Companies.CompanyName, CompanysCategories.CompanyID, CompanysCategories.SubCategoryID, Companies.Owner, Companies.ProductsServices, Companies.Address, Companies.Phone, Companies.Email, Companies.Website  FROM CompanysCategories INNER JOIN Companies  ON CompanysCategories.CompanyID = Companies.CompanyID WHERE " + sCond + " ORDER BY CompanyName ASC"
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()
rsResults_numRows = 0
%>

 

[PHV]

In your SQL code, replace CompanysCategories.CompanyID with Companies.CompanyID and get rid of CompanysCategories.SubCategoryID.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[agr1]

Thank you so much! Seems to be working...