SQL command fails to join tables

Question

I am new to oracle sql. I am trying to execute the following Query

SQL> select movie.movie_title, movie.release_year, cast.movie_title, cast.release_year
from MOVIE movie, CAST_MEMBER cast 



                                                                                                                                             *
ERROR at line 1:
ORA-00933: SQL command not properly ended

the query works If I don't join the actor table but I am trying to extract actor name from ACTOR table. I have looked at other problems but could not get it fixed. If someone could guide on where I am going wrong.

Thank you

wvdz · Accepted Answer · 2014-10-19 10:20:36Z

2

The where clause must come after all joins.

select movie.movie_title, movie.release_year, cast.movie_title, cast.release_year
from MOVIE movie, CAST_MEMBER cast 
join ACTOR actor on(actor.actor_name = cast.actor_name) 
where movie.movie_title = cast.movie_title and movie.release_year = cast.release_year 
group by cast.movie_title, cast.release_year, cast.actor_name;

Also, you shouldn't mix the two join notations, so it's better to write:

select movie.movie_title, movie.release_year, cast.movie_title, cast.release_year
from MOVIE movie
join CAST_MEMBER cast on (movie.movie_title = cast.movie_title and movie.release_year = cast.release_year)
join ACTOR actor on(actor.actor_name = cast.actor_name) 
group by cast.movie_title, cast.release_year, cast.actor_name;

answered Oct 19, 2014 at 10:20

wvdz

16.2k4 gold badges60 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maddy Over a year ago

Thank you for that, I will keep that in mind. Also ,the same question. On executing the query I get the error RROR at line 1: ORA-00979: not a GROUP BY expression Does that mean I need to include COUNT, SUM etc somewhere in the query? thank you for the response and help.

wvdz Over a year ago

@Mani take a look here for an explanation of that stackoverflow.com/a/1520641/2947592

Nick.Mc · Accepted Answer · 2014-10-19 10:22:27Z

2

I suggest you don't mix your join syntaxes:

select distinct 
movie.movie_title, movie.release_year, 
cast.movie_title, cast.release_year
from MOVIE movie
inner join CAST_MEMBER cast 
on movie.movie_title = cast.movie_title and movie.release_year = cast.release_year
inner join ACTOR actor 
on actor.actor_name = cast.actor_name;

I have also removed the group by and replaced it with distinct

answered Oct 19, 2014 at 10:22

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

3 Comments

wvdz Over a year ago

Since when is distinct better than group by? Did I miss the memo?

Maddy Over a year ago

@Nick.McDermaid, thank you for the quick reply, just had a follow up question, the query produces the following error, ERROR at line 1: ORA-00979: not a GROUP BY expression. Is that because group by expression works with count , sum etc? Do i need to include atleast one of them? I am trying to print the MOVIE_TITLE, RELEASE_YEAR and ACTOR_NAME from this query.

Nick.Mc Over a year ago

@popvitsj: personally I only use group by when the intention is to aggregate something else. If the intention is to remove duplicates I use distinct. That's my personal preference. There is no memo. Anyway, Mani, Did this error appear in your original query? It would have been nice to know that. What is your intention of using group by? Have you tried the query without using group by at all?

Collectives™ on Stack Overflow

SQL command fails to join tables

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related