ORA-00904 for one INNER JOIN but not for another

Question

for one particular query I've written up the ORA-00904 error has occurred

SELECT '103040698' as StudID, M.Title, R.SHORTDESC, C.COLOURNAME
FROM MOVIE0698 M
INNER JOIN RATING0698 R
ON M.SHORTDESC = R.SHORTDESC
INNER JOIN COLOURTYPE0698 C
ON M.COLOURNAME = C.COLOURNAME
ORDER BY Title ASC;

specfically for ON **M.**SHORTDESC = R.SHORTDESC

However, the previous query I had just written beforehand that used an INNER JOIN as well

SELECT '103040698' as StudID, A.FullName, M.Title, M.RelYear
FROM CASTING0698 C
INNER JOIN ACTOR0698 A
ON C.ActorNo = A.ActorNo
INNER JOIN MOVIE0698 M
ON C.MovieNo = M.MovieNo
ORDER BY RelYear DESC;

this query runs perfectly, so I'm just unclear what I've done wrong.

From what I remember INNER JOINs are written with the child table first then the parent table e.g. ON C.ActorNo = A.ActorNo but I could be wrong since I know some software will accept either way while others are sensitive to it.

for context, I'm using SQLJunior for my school work

here is the whole error

ON M.shortdesc = R.shortdesc

*

Error at line 4: ORA-00904: "M"."SHORTDESC": invalid identifier

Answer 1

In plain English the error message means "the MOVIES0698 table doesn't have a column called SHORTDESC"

Remember that Oracle column names are only case insensitive if they are not enclosed in quotes, or were quoted allcaps. If a column name is enclosed in quotes, and is not all uppercase, when it is created then it must forced more be referred to using quotes

SHORTDESC -- not case sensitive, you could write SELECT shortDesc 
"SHORTDESC" -- not case sensitive, because it's all caps anyway, can write SELECT shortdesc
"ShortDesc" -- case sens, must refer to this column using quotes forever more: SELECT "ShortDesc"... JOIN ON M."ShortDesc" etc

In summary, never enclosed oracle column names in quotes, for any reason. If you want to call a column something that is a reserved word, call it something else

If movies has no such column you have to join on a different column (SHORTDESC seems like it would be a text column, which probably makes for a poor join target anyway; is there no other column that is intended to relate movies and ratings better? Such as a "movieid" in the ratings table? It seems to me like one movie could have one or more ratings (some critics rate it high, others low, and you average the scores of many ratings..?)

Answer 2

I was using the wrong column names to join these tables together, i was writing it as

SELECT '103040698' as StudID, M.Title, R.SHORTDESC, C.COLOURNAME
FROM MOVIE0698 M
INNER JOIN RATING0698 R
ON M.SHORTDESC = R.SHORTDESC
INNER JOIN COLOURTYPE0698 C
ON M.COLOURNAME = C.COLOURNAME
ORDER BY Title ASC;

instead of

SELECT '103040698' as StudID, M.Title, R.SHORTDESC, C.COLOURNAME
FROM MOVIE0698 M
INNER JOIN RATING0698 R
ON M.ratingcode= R.ratingcode
INNER JOIN COLOURTYPE0698 C
ON M.colourcode= C.colourcode
ORDER BY Title ASC;

TL:DR I had to use the proper foreign/primary key for INNER JOIN