8 Replies Latest reply on May 19, 2016 12:59 PM by EddieLotter

    Assistance with a LEFT OUTER JOIN query

    WolfShade Level 4

      Hello, all,


      I am having some issues trying to put together an SQL query of an Oracle 11g database, and I could use some help.


      Let's say tableA is blog entries; tableC is comments for the blog entries; tableB is the associative table:


      blogID        blogTitle       blogBody      dateEntered
      1             This is a test  More text...  2016-05-20 11:11:11
      2             More testing    Still more!   2016-05-19 10:10:10
      3             Third charm!!   Blah, blah.   2016-05-18 09:09:09


      commID        userID          userText      dateEntered
      10            Bravo           I like it!    2016-05-20 11:21:31
      11            Charlie         I don't!      2016-05-20 11:31:51
      12            Alpha           Do it again!  2016-05-19 10:20:30
      13            Bravo           Still more?   2016-05-19 10:30:50
      14            Charlie         So, what?     2016-05-19 10:35:45
      15            Bravo           Blah, what?   2016-05-18 09:10:11
      16            Alpha           Magic number! 2016-05-18 09:11:13


      blogID        commID
      1             10
      1             11
      1             12
      2             13
      2             14
      3             15
      3             16


      I'm attempting to get blogID, blogTitle, blogBody, and a count of the number of comments for each blog entry.  But, since I'm using to_char() for the date, and COUNT(commID) for the total number of comments, I'm getting "not a group by expression".


      Here is a pseudo-SQL example of what I am trying.


      SELECT a.blogID, a.blogTitle, a.blogBody, to_char(a.dateEntered,'YYYY-MM-DD HH24:MI:SS') as dateEntered, COUNT(c.commID) as total
      FROM tableA a LEFT OUTER JOIN tableB b ON b.blog_ID = a.blog_ID
                    LEFT OUTER JOIN tableC c ON c.commID = b.commID
      WHERE a.blogID = '1'
      GROUP BY blogID, blogTitle, blogBody
      ORDER BY to_date(dateEntered,'MM-DD-YYYY HH24:MI:SS') desc


      I'm pretty sure it's something simple, but I am just NOT seeing it.  Can you help?