Help with correlated sub queries

  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with correlated sub queries

    Hello, here is my query. the actual query is a little more complicated but similar... It returns an error because it cannot find the first query variable - s1.ShopCity

    any help is appreciated. thank you!!!

    (select count(*) from (select ShopID > 1 from tblShops s2 where s2.ShopCity = s1.ShopCity) as totalcount) as tcount

    from tblShops s1
    group by s1.ShopCity

  • #2
    And you are sure that s1.ShopCity is the correct spelling and that it is present in the tblShops table?

    But regardless I recommend you to rewrite your query to something like:

    select city.city ,count(s1.ShopCity) as tcountfrom ( SELECT s2.ShopCity as city FROM tblShop s2 GROUP BY s2.ShopCity ) as cityLEFT JOIN tblShop s1 ON city.city = s1.ShopCity AND s1.ShopID > 1group by city.city

    The reason is that the derived table city will only be evaluated once and reduced to all unique cities = fewer records, while in your case the subselect will be evaluated for every row of the tblShop table.