|
I am having a problem with a sql query. I'd like for it to only show one listing under an advertiserID even if it has multiple subcategories.
Here's my code:
mySQL = "Select distinct listingtype, businessname, advertisersubcategory.advertiserid, contactperson, address, category, city, state, zip, phone, fax, tollphone, cellphone, subcategoryid, website, showemail, email From Advertiser, AdvertiserSubCategory, Categories, County, City"
mySQL = mySQL & " where Advertiser.AdvertiserID = AdvertiserSubCategory.AdvertiserID"
mySQL = mySQL & " and Advertiser.CategoryID = Categories.CategoryID"
mySQL = mySQL & " and Advertiser.CountyID=County.CountyID"
mySQL = mySQL & " and Advertiser.CityID = City.CityID"
mySQL = mySQL & " and Advertiser.CountyID = '" & strCountyID & "'"
mySQL = mySQL & " and Advertiser.CategoryID = 8"
mySQL = mySQL & " and Approve =1"
mySQL = mySQL & " Order By ListingType, BusinessName"
The query only returned two listings when I removed subcategoryid, but I really need it to be in this query. It shows three listings with the subcategoryid field. Here's the table schema:
businesssname: subcategoryid: advertiserid:
Test Real Estate 13 &n bsp; 26
Sample Real Estate 13 &n bsp; 31
Sample Real Estate 56 &n bsp; 31
I would like for Sample Real Estate to only show up one time with the subcategoryid field. How do I get this to work? I need the subcategoryid field because it is being used in an important link for this listing.
|