•  

Announcement

Announcement Module
Collapse
No announcement yet.

Help with correlated sub queries

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with correlated sub queries

    #1
    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
    s1.ShopCity,
    (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
    Tags: None

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

    Comment

    Working...
    X