Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Stumped on SQL

  • 05-01-2008 03:00PM
    #1
    Registered Users, Registered Users 2 Posts: 883 ✭✭✭


    I'm trying to use a subquery in the sql below but not having any luck. Anyone spot my error?

    Basically, the subquery will return something like:

    loc1 dirs1 2
    loc2 dirs2 1
    loc1 dirs1 3

    and then I want the main query to give:

    loc1 5
    loc2 1

    sSQL = "SELECT [mainlist].Location, Sum(CountofNCP) AS SumofNCP FROM (SELECT [mainlist].Location, dirs.directions, Count(mainlist.ncp_tt) AS CountOfNCP FROM mainlist INNER JOIN dirs ON [mainlist].Location = dirs.areadirs GROUP BY 'Location, [mainlist].Level, dirs.directions HAVING [mainlist].Level = " & mylevel_form & " OR [mainlist].Level = " & mylevel_form -1 & " OR [mainlist].Level = " & mylevel_form +1 & ") ;"


Comments

  • Moderators, Politics Moderators, Paid Member Posts: 44,066 Mod ✭✭✭✭Seth Brundle


    As you don't say what is going wrong, I'm just guessing the following:
    The single quote at 'Location is the source.
    sSQL = "
    SELECT [mainlist].Location, Sum(CountofNCP) AS SumofNCP 
    FROM (
         SELECT [mainlist].Location, dirs.directions, Count(mainlist.ncp_tt) AS CountOfNCP 
         FROM mainlist 
         INNER JOIN dirs ON [mainlist].Location = dirs.areadirs 
         GROUP BY [B]'Location[/B], [mainlist].Level, dirs.directions 
         HAVING [mainlist].Level = " & mylevel_form & " 
         OR [mainlist].Level = " & mylevel_form -1 & " 
         OR [mainlist].Level = " & mylevel_form +1 & ") ;"
    


  • Registered Users, Registered Users 2 Posts: 883 ✭✭✭sirpsycho


    The quote at 'Location was part of the problem but I forgot to add in the GROUP BY for the main query.


Advertisement