Needs Help With SQL Query Results

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   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. 