1
SELECT 
    S.MESSAGE_ID, S.USERNAME, C.count(MESSAGE_ID)
FROM 
    MESSAGE S, MESSAGE_LIKE C
WHERE
    S.MESSAGE_ID = C.MESSAGE_ID 
GROUP BY 
    MESSAGE_ID;

I'm getting:

ORA-00918: column ambiguously defined

  1. 00000 - "column ambiguously defined"
    *Cause:
    *Action: Error at Line: 13 Column: 11
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user5599408
  • 19
  • 1
  • 2
    `C.count(MESSAGE_ID)` is wrong, you need `count(C.MESSAGE_ID)` – Tony Andrews Apr 04 '16 at 11:27
  • 1
    or `COUNT(*)` would do. – Tony Andrews Apr 04 '16 at 11:28
  • Please take the [tour](http://stackoverflow.com/tour) and read [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask). Then come back and edit your question accordingly. – Jan Apr 04 '16 at 11:29
  • @TonyAndrews Actually its not enough here. – sagi Apr 04 '16 at 11:37
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Apr 04 '16 at 12:39

2 Answers2

2

I didn't know such syntax even exists in oracle, would have guess it will throw an error c.count(message_id) unknown... Anyway,if you want to do something with a column, that exists in more then one table in your query, you have to specify which column you want to take, just like you did on your where clause. So you have two places, 1) the count 2) the group by clause.

 SELECT S.MESSAGE_ID, S.USERNAME, count(c.MESSAGE_ID)
 FROM MESSAGE S
 INNER JOIN MESSAGE_LIKE C
 ON (S.MESSAGE_ID=C.MESSAGE_ID )
 GROUP BY S.MESSAGE_ID,S.username;

Also, you have another issue with your query which is that in ORACLE you have to specify in the group by clause every column that you specified on the select, and is not being used with an aggregation function.

And lastly, I've changed your join syntax from implicit(comma separated) to explicit join syntax. Please avoid this join syntax as it will lead to mistakes when joining more then two tables/left joining. Explicit join syntax are easier to follow.

sagi
  • 40,026
  • 6
  • 59
  • 84
0

Please have a look on this page

Oracle Joins - Comparison between conventional syntax VS ANSI Syntax

Community
  • 1
  • 1
Jahangir Alam
  • 801
  • 8
  • 21