Assistance with a LEFT OUTER JOIN query

[WolfShade]

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,

^_^

[EddieLotter]

You're using to_char in the select clause and to_date in the order by clause. Make them the same and you should be good to go, but then again, it is Oracle.

Cheers

Eddie

[EddieLotter]

Wait, I should have looked closer.

to_char is not an aggregate function, so you have to include it in the group by clause.

Sorry about that.

Cheers

Eddie

[WolfShade]

I have aliased it, and tried using the alias and tried it using the column name, no change.

GROUP BY a.blogID, a.blogTitle, a.blogBody

GROUP BY a.blogID, a.blogTitle, a.blogBody, a.dateEntered --column name with table alias

GROUP BY a.blogID, a.blogTitle, a.blogBody, dateEntered --alias

I get varying error messages on each.

V/r,

^_^

[EddieLotter · Accepted solution]

Try:

GROUP BY a.blogID, a.blogTitle, a.blogBody, to_char(a.dateEntered,'YYYY-MM-DD HH24:MI:SS')

Cheers

Eddie

[WolfShade]

AH!  Never thought of that.  I'll give it a shot.

Thanks!

V/r,

^_^

[WolfShade]

Okay.. that seems to have fixed it, but the query is now breaking elsewhere.. of course. 

Part of the problem (and don't ask me WHY the original developer did this, coz it makes ZERO sense) is that one of the columns (the body of the blog) is CLOB/BLOB, and if I include it in the group by, I get inconsistent datatypes, if I don't include it, I get the "not a group by expression".

*headdesk* *headdesk* *headdesk* *headdesk* *headdesk* *headdesk* *headdesk* *headdesk*

^_^

[EddieLotter]

That's why I recommend that people post their database questions in a forum that deals specifically with their database engine.

I'm not an Oracle guy, I could only answer your original question because it dealt with ANSI SQL.

Cheers

Eddie

[WolfShade]

Sorry.. I wasn't paying attention while typing.  The to_date() _should_ be to_char(). 

V/r,

^_^