Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25

    Angry Unanswered: Need Query Help (with a COUNT(*) for listings) please..

    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!
    --

    Travis Nelson
    http://www.travis-nelson.com/

  2. #2
    Join Date
    Jul 2004
    Posts
    45
    use group by function
    SELECT cityID, city, count(*) FROM listings group by cityID,city

  3. #3
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25

    Unhappy Oops

    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.
    --

    Travis Nelson
    http://www.travis-nelson.com/

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How about:
    Code:
    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

  5. #5
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25

    Talking It Worked!!!

    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!!!! =)
    --

    Travis Nelson
    http://www.travis-nelson.com/

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25

    Thumbs up Tweakages

    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!
    --

    Travis Nelson
    http://www.travis-nelson.com/

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  9. #9
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25

    Smile Thanks again

    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.
    --

    Travis Nelson
    http://www.travis-nelson.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •