[freekMEDIA]

Hey all, I have a problem and it's really making my head spin. If someone could help it would be SO appreciated. I am using an Access DB.

I have the following tables:

listings
- listingID
- cityID (foreign key)
- username (foreign key)
- description
- etc.

listingtypes
- listingtypeID
- listingtype

listingtypelink (linking table for many-many relationship between listings and listingtypes)
- listingID (foreign key)
- listingtypeID (foreign key)

cities
- cityID
- city
- stateID (foreign key)

states
- stateID
- state

members
- username
- password
- etc.

Now my problem, I need to do a SELECT wherein I can select unique city and city ID from the cities table, as well as a COUNT() of the number of listings for each of the cities.

Ideally, results would look like this if I selected the cities that had listings that were of type X (from the listingtypes table, listingtype can be more than one value too such as 12,34,45,46):
Fort Myers (10)
Port Charlotte (1)
Naples (13)
Tampa (26)
etc...

(In other words, I need it to be able to "SELECT DISTINCT cityID, city, numberOfTotalListings FROM listings WHERE cityID = [a single value, such as 233] AND [the listing is active and not disabled] AND [the listing type is either 23 or 45 or 56 or 57 or even a combination of any of these]")

I can't get the count to come out right, no matter what I do, and I just think I am missing something due to my lack of experience with these types of queries.

I have tried nested selects to return a value, which I have had success with in the past, and I know in some way this is the method to use, but I can't figure out the proper query:
strSelect = "SELECT cities.cityID, cities.city, (SELECT COUNT(*) FROM listings WHERE listings.cityID = cities.cityID) AS listingNumber FROM (((cities INNER JOIN listings ON cities.cityID = listings.cityID) INNER JOIN states ON cities.stateID = states.stateID) INNER JOIN members ON listings.username = members.username) LEFT JOIN listingtypelink ON listings.listingID = listingtypelink.listingID WHERE states.stateID = @stateID AND listings.listingdisabled = False AND members.accountdisabled = False AND listingtypelink.listingtypeID IN (" & objListingType & ")

I can't get the count to come out right. Can someone help me to shed some light on this, please? I would be forever grateful...

My problem seems to lie in the fact that there can be more than one listing type at a time (i.e. - 2,4,5,6,7,20) but I still need the distinct count for the cities, which I can't seem to figure out.

Thank you so much!

[deebee]

use group by function
SELECT cityID, city, count(*) FROM listings group by cityID,city

[freekMEDIA]

Sorry, my bad, I forgot to add the GROUP BY to my query in my post, I am already using the GROUP BY clause in my statement exactly as you stated (group by cityID, city)

I just can't seem to get it to grab the correct records and count.

This probably sounds really lame/bad, but if someone has the time, could they write out a of what I need if they know what it might be? I'm normally pretty good at this, but I've been on this simple little problem for 2 days and I am still just as stuck as ever, I just don't know what else to do.... =(

Thank you guys... very much.

[Pat Phelan]

How about:

SELECT Count(*), cities.city
   FROM listings
   JOIN cities
      ON (cities.cityID = listings.cityID)
   WHERE EXISTS (SELECT *
      FROM listingtypelink
      WHERE  listingtypelink.listingID = listings.listingID
         AND listingtypeID IN (12,34,45,46))

-PatP

[freekMEDIA]

THANK YOU SO MUCH!!

You have no idea how awesome it was for you to take the time to write that out for me, I just copied and pasted it, and made a couple small tweaks and it worked perfectly!

Did not know I could use WHERE EXISTS, will have to look into this more.

THANK YOU THANK YOU THANK YOU!!!! =)

[Pat Phelan]

I'm just curious, but what did you need to tweak?

I couldn't test the code that I posted because I didn't have your MDB file, but nothing jumps out at me as being tweakable. I'd like to know if I missed something so that I get it right next time.

-PatP

[freekMEDIA]

Sorry, when I said tweak, I just kinda meant pasting it and then taking out the line breaks, and changing the numbers to a variable. Below is my finished code (In ASP.NET VB)
It's a little bit sloppy, but it works and at this point, that's all that matters. =)

'***********************************

strSelect = "SELECT COUNT(*) AS listingNumber, cities.city, cities.cityID FROM (listings INNER JOIN cities ON cities.cityID = listings.cityID) WHERE EXISTS (SELECT * FROM listingtypelink WHERE listingtypelink.listingID = listings.listingID"

If objListingType <> "all" Then
strSelect &= " AND listingtypelink.listingtypeID IN (" & objListingType & ")"
End If
strSelect &= ") AND cities.stateID = @stateID"
strSelect &= " GROUP BY cities.cityID, cities.city"
strSelect &= " ORDER BY cities.city ASC"

'***********************************

Thanks again!

[Pat Phelan]

Ah, I see. I was trying to figure out what I might have broken somehow, and you were talking about the cleanup needed to make it work with your application in VB. Now we're on the same page again!

-PatP

[freekMEDIA]

hah yeah for sure, sorry, sometimes I just type and don't even really pay attention to what I'm saying (that's the worst part about being a fast typer!)

Thanks so much again, I have since taken that snippet of code and used it on a few other pages for the site. Provided for much optimization as well with only single calls to the database (don't even ask what I was doing before, haha, bad practices for sure.)

Take care.