Assistance with a LEFT OUTER JOIN query
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:
tableA
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
tableC
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
tableB
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?
V/r,
^_^
AdChoices