Filter or Nested Select statment?

  • LUMunicipality table consists of

    id, Name, Main fields

    Would like to further filter results from the following:

    Select LUMunicipality.Name AS Mun,COUNT(PROPERTIES.Municipality)total

    From LUMunicipality,PROPERTIES Where LUMunicipality.Name =

    PROPERTIES.Municipality Group By LUMunicipality.Name

    returns

    MUN total

    city1 17

    city2 1

    city3 1

    city4 34

    city5 15

    city6 16

    city7 18

    city8 20

    city9 27

    city10 1

    city11 5

    I need to further filter this result set based on a string value in the 'Main' field in the LUMunicipality table

    ie; where Main = 'a string value'

    appending HAVING will not work with strings..correct?

    Any direction would be appreciated. Thanks

  • It sounds like you just want the count of properties from a specific municipality so your query should be this (note, I changed to standard ANSI join syntax as the old syntax you used will be deprecated):

    Select

    LUMunicipality.Name AS Mun,

    COUNT(PROPERTIES.Municipality) total

    From

    LUMunicipality JOIN

    PROPERTIES ON

    LUMunicipality.Name = PROPERTIES.Municipality

    WHERE

    LUMunicipality.NAME = 'String'

    Group By

    LUMunicipality.Name

    You definitely want the filter in the WHERE as the WHERE is applied BEFORE the GROUP BY so it is more efficient than using HAVING which is applied AFTER the grouping.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply