CODEWITHSUNDEEP
mysqlsql
KAKAK
KAKAK

Reputation: 899

Using join to retrieve a row from 2 tables in SQL

SELECT * FROM movie_time AS a,movie_db AS b, movie_actor AS c
WHERE a.Movie_ID = b.Movie_ID AND b.Movie_ID=c.Movie_ID 
      AND c.Movie_ID = a.Movie_ID AND Movie_ID =17

Hi i am trying to use the join function to retrieve the row which is working unless when i put the AND Movie_ID =17 clause i am not able to retrieve the row which corresponds to Movie_ID =17

I am using mySQL workbench. Can I also use the similar statement to also delete the particular row retreived?

The error: Error code 1052 Column 'Movie_ID' in where clause is ambiguos

Upvotes: 1

Views: 71

Answers (4)

Strawberry
Strawberry

Reputation: 33935

This returns more columns than you need but consider a syntax along these lines...

SELECT * 
  FROM movie_time mt
  JOIN movie_db m
    ON m.Movie_ID = mt.Movie_ID 
  JOIN movie_actor ma
    ON ma.Movie_ID = m.Movie_ID
 WHERE m.Movie_ID = 17;

Note that any "actorless" movies will not be returned!

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT * FROM movie_time AS a,movie_db AS b, movie_actor AS c
WHERE a.Movie_ID = b.Movie_ID AND b.Movie_ID=c.Movie_ID 
      AND c.Movie_ID = a.Movie_ID AND a.Movie_ID =17

You have not specified alias name for Movie_ID =17 resulting in ambiguity. As all the mentioned tables have column Movie_id , SQL can't decide which tables Movie_id column you are refering to when using the statement Movie_id=17. Change it to a.Movie_ID =17 if you want Movie_id=17 from table movie_time .

As a side note , you should do this by using INNER JOIN .

Upvotes: 1

Stephan
Stephan

Reputation: 8090

You need to specify the alias a.Movie_ID =17:

SELECT * FROM movie_time AS a,movie_db AS b, movie_actor AS c
WHERE a.Movie_ID = b.Movie_ID AND b.Movie_ID=c.Movie_ID 
      AND c.Movie_ID = a.Movie_ID AND a.Movie_ID =17

And for the delete (assuming you want to delete from movie_time table):

DELETE
    a
FROM 
    movie_time AS a 
INNER JOIN movie_db AS b
    ON a.Movie_ID = b.Movie_ID
INNER JOIN movie_actor AS c
    ON c.Movie_ID = a.Movie_ID
WHERE 
    a.Movie_ID =17

Upvotes: 3

Ed Gibbs
Ed Gibbs

Reputation: 26333

The answers about aliasing are correct. In addition to the fix, I'd also like to encourage you to use ANSI joins, which will clean up your WHERE clause and let you split the filtering logic (Movie_ID = 17) from the join logic (all the rest of the noise in the WHERE clause).

This will do the same as the queries above:

SELECT * FROM movie_time AS a
  INNER JOIN movie_db AS b ON a.Movie_ID = b.Movie_ID
  INNER JOIN movie_actor AS c ON b.Movie_ID=c.Movie_ID
  WHERE a.Movie_ID =17

You don't need the AND c.Movie_ID = a.Movie_ID part of the WHERE because you're already joining a to c through b.

Upvotes: 1

Related Questions