SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: SQL prob

  1. #1
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL prob

    ok, im having a little trouble with a few queries i want to combine. im using MS ACCESS, btw. here they are...

    Code:
    SELECT     A.MessageBoardName,
               A.MessageBoardGroup,
               A.NumPostsPerPage,
               A.NumPostsPerThread,
               A.NumPagesAllowed,
               A.Per_Read            AS MB_Per_Read,
               A.Per_Post            AS MB_Per_Post,
               A.InheritPermissions,
               B.Per_Read            AS G_Per_Read,
               B.Per_Post            AS G_Per_Post,
               B.GroupName
    FROM       MessageBoardList      A
    INNER JOIN GroupList             B
    ON         A.MessageBoardGroup   = B.GroupID
    WHERE      A.MessageBoardID      = #Template.Variables.Page.BoardID#
    and...

    Code:
    SELECT COUNT(MessageID) AS TotalBoardMessages
    FROM   MessageList
    WHERE  MessageBoardID   = #Template.Variables.Page.BoardID#
    obviously i cant use an inner join. i thought i might be able to do this with an outer join, but i cant seem to get the syntax correct. too bad the ms access docs are nearly useless when it comes to raw sql.

    anyway, is it even possible to combine these queries?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, it's possible, but don't do it-- they don't cover the same set of records, so you'd need to "uncombine" the information carefully after the query

    it's probably okay just to run them one after the other
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what do you mean by "uncombine"? if this realy isnt a good idea i would like to understand why. with that in mind, do you think you could show me how to do what i originaly asked?

    thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    METHOD 1: second query as a column subquery

    this works because the second is a scalar query, returning only one value, and this one value is returned for every row of the outer query

    a competent optimizer will not re-retrieve the subquery for every row of the outer query

    note: this is standard sql syntax but Access may not support it
    Code:
    SELECT A.MessageBoardName
         , A.MessageBoardGroup
         , A.NumPostsPerPage
         , A.NumPostsPerThread
         , A.NumPagesAllowed
         , A.Per_Read   AS MB_Per_Read
         , A.Per_Post   AS MB_Per_Post
         , A.InheritPermissions
         , B.Per_Read   AS G_Per_Read
         , B.Per_Post   AS G_Per_Post
         , B.GroupName
         , ( SELECT COUNT(MessageID) 
               FROM MessageList
              WHERE MessageBoardID 
                  = #Template.Variables.Page.BoardID#
           ) AS TotalBoardMessages 
      FROM MessageBoardList   A
    INNER 
      JOIN GroupList          B
        ON A.MessageBoardGroup = B.GroupID
     WHERE A.MessageBoardID 
         = #Template.Variables.Page.BoardID#
    METHOD 2: second query as a table subquery

    this works because the second query returns only one row, which is joined to every row produced by the first query

    note: this too is standard sql but Access may not support it

    Code:
    SELECT A.MessageBoardName
         , A.MessageBoardGroup
         , A.NumPostsPerPage
         , A.NumPostsPerThread
         , A.NumPagesAllowed
         , A.Per_Read   AS MB_Per_Read
         , A.Per_Post   AS MB_Per_Post
         , A.InheritPermissions
         , B.Per_Read   AS G_Per_Read
         , B.Per_Post   AS G_Per_Post
         , B.GroupName
         , CountQuery.TotalBoardMessages 
      FROM MessageBoardList   A
    INNER 
      JOIN GroupList          B
        ON A.MessageBoardGroup = B.GroupID
    INNER
      JOIN ( SELECT COUNT(MessageID) 
                      AS TotalBoardMessages
               FROM MessageList
              WHERE MessageBoardID 
                  = #Template.Variables.Page.BoardID#
           ) AS CountQuery 
     WHERE A.MessageBoardID 
         = #Template.Variables.Page.BoardID#
    METHOD 3: use a GROUP BY

    this method works (produces the correct results) only if MessageBoardList is many-to-one with GroupList, i.e. each MessageBoardList row has exactly one matching GroupList record (probably true in your case)

    each MessageBoardList/GroupList row is joined to all matching MessageList rows, and the count is produced by grouping

    note: this works fine in Access

    Code:
    SELECT A.MessageBoardName
         , A.MessageBoardGroup
         , A.NumPostsPerPage
         , A.NumPostsPerThread
         , A.NumPagesAllowed
         , A.Per_Read   AS MB_Per_Read
         , A.Per_Post   AS MB_Per_Post
         , A.InheritPermissions
         , B.Per_Read   AS G_Per_Read
         , B.Per_Post   AS G_Per_Post
         , B.GroupName
         , COUNT(MessageID)
              AS TotalBoardMessages 
      FROM MessageBoardList   A
    INNER 
      JOIN GroupList          B
        ON A.MessageBoardGroup = B.GroupID
    INNER
      JOIN MessageList
        ON A.MessageBoardID = B.GroupID
     WHERE A.MessageBoardID = MessageList.MessageBoardID[/COLOR]
         = #Template.Variables.Page.BoardID#
    GROUP 
        BY A.MessageBoardName
         , A.MessageBoardGroup
         , A.NumPostsPerPage
         , A.NumPostsPerThread
         , A.NumPagesAllowed
         , A.Per_Read 
         , A.Per_Post 
         , A.InheritPermissions
         , B.Per_Read 
         , B.Per_Post 
         , B.GroupName
    METHOD 4: use a UNION

    this works by shoehorning the results of the second query into the same result table structure as that of the first

    the columns in a UNION have to be datatype-compatible, so you may need to use CAST(NULL AS dataytpe) to ensure this

    note: this works fine in Access

    this is the method i had in mind when i said you have to "uncombine" the results -- but this method is a very useful strategy because it can be used for combining result sets from various sources that share similar structures
    Code:
    SELECT A.MessageBoardName
         , A.MessageBoardGroup
         , A.NumPostsPerPage
         , A.NumPostsPerThread
         , A.NumPagesAllowed
         , A.Per_Read   AS MB_Per_Read
         , A.Per_Post   AS MB_Per_Post
         , A.InheritPermissions
         , B.Per_Read   AS G_Per_Read
         , B.Per_Post   AS G_Per_Post
         , B.GroupName
      FROM MessageBoardList   A
    INNER 
      JOIN GroupList          B
        ON A.MessageBoardGroup = B.GroupID
     WHERE A.MessageBoardID 
         = #Template.Variables.Page.BoardID#
    UNION ALL
    SELECT null
         , null
         , COUNT(MessageID) 
         , null
         , null
         , null
         , null
         , null
         , null
         , null
         , null
      FROM MessageList
     WHERE MessageBoardID 
         = #Template.Variables.Page.BoardID#
    notice that the count of MessageIDs is numeric, so i stuck it in the thrid column, assumed numeric

    my advice? go ahead and submit two separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Mar 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool thanks a lot. so you realy know your sql, huh?

    anyway, the first meathod works with access. im pretty sure the second one will to if i fiddle a bit.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    cool thanks a lot. so you really know your sql, huh?
    you're welcome

    yeah, i know sql pretty well, i've been using it for fifteen years

    ...but do you think i can find a job?

    [sigh]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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