SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble with Join and Correlated Subquery

    I'm not sure if this is possible but I'm trying to right a compound query as seen below. I'm running in trouble in trying to include the second select in the from clause.

    Code:
    SELECT * FROM Tickets t JOIN Computers ON t.cid=Computers.cid JOIN Sites ON Computers.sid = Sites.sid, (SELECT COUNT(a.tid) FROM Actions a WHERE a.tid = t.tid GROUP BY a.tid) As ActionCount WHERE t.pid = 15 ORDER BY t.submitDate DESC;
    In this example I get an error saying "Unknown column 't.tid' in 'where clause'".

    I'm not quite sure how to handle the various alias when dealing with multiple joins and a correlated sub query.

    Thanks for any suggestions you might offer.
    Follow Me On Twitter: BryceRay

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bar338 View Post
    I'm not sure if this is possible but I'm trying to right a compound query as seen below. I'm running in trouble in trying to include the second select in the from clause.

    Code:
    SELECT * FROM Tickets t JOIN Computers ON t.cid=Computers.cid JOIN Sites ON Computers.sid = Sites.sid, (SELECT COUNT(a.tid) FROM Actions a WHERE a.tid = t.tid GROUP BY a.tid) As ActionCount WHERE t.pid = 15 ORDER BY t.submitDate DESC;
    In this example I get an error saying "Unknown column 't.tid' in 'where clause'".

    I'm not quite sure how to handle the various alias when dealing with multiple joins and a correlated sub query.

    Thanks for any suggestions you might offer.
    Looks like your intention is to use t as an alias for the tickets table, you just forgot to specify that in the query:

    Code SQL:
    SELECT * FROM Tickets AS t JOIN Computers ON t.cid=Computers.cid JOIN Sites ON Computers.sid = Sites.sid, (SELECT COUNT(a.tid) FROM Actions a WHERE a.tid = t.tid GROUP BY a.tid) AS ActionCount WHERE t.pid = 15 ORDER BY t.submitDate DESC

    The syntax for defining table aliases is:

    table_name AS alias_name
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star  
      FROM Tickets AS t 
    INNER
      JOIN Computers 
        ON Computers.cid = t.cid
    INNER 
      JOIN Sites 
        ON Sites.sid = Computers.sid
    INNER
      JOIN ( SELECT tid
                  , COUNT(*) AS ActionCount
               FROM Actions 
             GROUP
                 BY tid ) AS aaa
        ON aaa.tid = t.tid
     WHERE t.pid = 15 
    ORDER 
        BY t.submitDate DESC
    you asked for suggestions, so my main suggestion is to learn how not to write a complex query on one humoungously long line, there's no way you will ever be able to understand it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star  
      FROM Tickets AS t 
    INNER
      JOIN Computers 
        ON Computers.cid = t.cid
    INNER 
      JOIN Sites 
        ON Sites.sid = Computers.sid
    INNER
      JOIN ( SELECT tid
                  , COUNT(*) AS ActionCount
               FROM Actions 
             GROUP
                 BY tid ) AS aaa
        ON aaa.tid = t.tid
     WHERE t.pid = 15 
    ORDER 
        BY t.submitDate DESC
    you asked for suggestions, so my main suggestion is to learn how not to write a complex query on one humoungously long line, there's no way you will ever be able to understand it
    Just a little thing to note bar338, the dreaded, evil SELECT * does have a place initially whilst you are making the joins, to verify that the joins are working as expected but once you have confirmed that the joins are ok, you should replace it by specifying the fields that you want only.

    Code SQL:
    SELECT
         TABLE_NAME.field_name AS field_alias
         ....the other FIELDS being selected

    The use of an alias makes it easier when referring to that field in other clauses in the query.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    the dreaded, evil SELECT * does have a place initially whilst you are making the joins, to verify that the joins are working as expected but once you have confirmed that the joins are ok, you should replace it by specifying the fields that you want only.
    this is ~awesome~

    Quote Originally Posted by SpacePhoenix View Post
    The use of an alias makes it easier when referring to that field in other clauses in the query.
    not always -- you can reference the alias only in the GROUP BY, HAVING, and ORDER BY clauses

    not being able to reference a column alias in the WHERE clause is a major nuisance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help everyone. I eventually got it working and hopefully using proper technique

    Code SQL:
                    SELECT t.tid,submitDate,closedDate,tDesc,model,OS,RAM,name,location,COUNT
    FROM Tickets AS t
    JOIN Computers AS c
        ON t.cid=c.cid
    JOIN Sites AS s
        ON c.sid = s.sid
    JOIN (SELECT COUNT(tid) AS COUNT, tid 
                 FROM Actions 
                 GROUP BY tid) 
                 AS ac
        ON ac.tid=t.tid
    WHERE t.pid = ? 
    ORDER
        BY t.submitDate DESC;
    Follow Me On Twitter: BryceRay

  7. #7
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bar338 View Post
    Thanks for your help everyone. I eventually got it working and hopefully using proper technique

    Code SQL:
                    SELECT t.tid,submitDate,closedDate,tDesc,model,OS,RAM,name,location,COUNT
    FROM Tickets AS t
    JOIN Computers AS c
        ON t.cid=c.cid
    JOIN Sites AS s
        ON c.sid = s.sid
    JOIN (SELECT COUNT(tid) AS COUNT, tid 
                 FROM Actions 
                 GROUP BY tid) 
                 AS ac
        ON ac.tid=t.tid
    WHERE t.pid = ? 
    ORDER
        BY t.submitDate DESC;
    I've actually realized one problem which I don't quite understand. The problem occurs with the third join that has a sub query:
    Code SQL:
    JOIN (SELECT COUNT(tid) AS COUNT, tid 
                 FROM Actions 
                 GROUP BY tid) 
                 AS ac
        ON ac.tid=t.tid
    This select looks to see if there are any actions associated with the ticket. Sometimes there are no actions with a ticket. In this instance it should simply return a count of 0 to list with the row. However, what is actually happening is that the row as a whole is not returned if there are 0 actions associated with it.
    I'm assuming this has something to do with the nature of joins. However, I'm kind of new to the concept of joins so I may be missing something.
    Follow Me On Twitter: BryceRay

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    stick the words LEFT OUTER in front of the JOIN to the ac subquery

    and in the SELECT clause for the outer query, change count to COALESCE(ac.count,0) AS count
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    stick the words LEFT OUTER in front of the JOIN to the ac subquery

    and in the SELECT clause for the outer query, change count to COALESCE(ac.count,0) AS count
    This is what I did:
    Code SQL:
    LEFT OUTER JOIN (SELECT COALESCE(ac.COUNT,0) AS COUNT, tid FROM Actions GROUP BY tid) AS ac

    However this part does not make logical sense because ac.count hasn't been created at the point it is referenced in coalesce.
    Code SQL:
     COALESCE(ac.COUNT,0) AS COUNT

    I looked up the coalesce function and think I understand its purpose. I tried replacing the above with.
    Code SQL:
     COALESCE(COUNT(tid),0) AS COUNT
    However, this does not work either.

    The LEFT OUTER JOIN fixed it so that it correctly displays all rows which have a count of 0 actions. However, in this instance when I try to display count nothing is displayed rather than 0. In the above use of COALESCE if the count is null it should display the second value. However, this does not seem to be working.
    Follow Me On Twitter: BryceRay

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the COALESCE should be on the outer query --
    Code:
    SELECT t.tid
         , submitDate
         , closedDate
         , tDesc
         , model
         , OS
         , RAM
         , name
         , location
         , COALESCE(ac.count,0) AS count
      FROM Tickets AS t
    INNER
      JOIN Computers AS c
        ON t.cid=c.cid
    INNER
      JOIN Sites AS s
        ON c.sid = s.sid
    LEFT OUTER
      JOIN ( SELECT COUNT(tid) AS count
                  , tid 
               FROM Actions 
            GROUP 
                 BY tid ) AS ac
        ON ac.tid=t.tid
     WHERE t.pid = ? 
    ORDER
        BY t.submitDate DESC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh ok.. That makes complete sense now. Thanks for your help, I appreciate it.
    Follow Me On Twitter: BryceRay


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
  •