Highlighted

Assistance with a LEFT OUTER JOIN query

LEGEND ,
May 19, 2016

Copy link to clipboard

Copied

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,

^_^

Try:

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

Cheers

Eddie

Views

204

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Assistance with a LEFT OUTER JOIN query

LEGEND ,
May 19, 2016

Copy link to clipboard

Copied

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,

^_^

Try:

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

Cheers

Eddie

Views

205

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 19, 2016 0
Advocate ,
May 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 0
Advocate ,
May 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 0
LEGEND ,
May 19, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 0
Advocate ,
May 19, 2016

Copy link to clipboard

Copied

Try:

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

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 0
LEGEND ,
May 19, 2016

Copy link to clipboard

Copied

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

Thanks!

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 0
LEGEND ,
May 19, 2016

Copy link to clipboard

Copied

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*

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 0
Advocate ,
May 19, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 1
LEGEND ,
May 19, 2016

Copy link to clipboard

Copied

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

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 19, 2016 0