[RESOLVED] SQL SELECT queries question

[MoE70]

i need to show title, year of movies and order them by rank. ive tried:

Select title, year FROM movies, ratings ORDER BY rank;

it works but everything is listed 4 times. anyone know why?

[Pradeep1210]

Use a JOIN.

SELECT title, year
FROM movies
INNER JOIN ratings on movies.movieid=ratings.movieid
ORDER BY rank

[MoE70]

works great thanks

[Pradeep1210]

9) Show the names of all the actors who have not worked with director ‘Paul Greengrass’.

SELECT * FROM actors where actorid not in
(SELECT movie2actors.actorid FROM movie2actors
INNER JOIN movies2directors ON movie2actors.movieid=movies2directors.movieid
INNER JOIN directors ON movies2directors.directorid=directors.directorid
where directors.name = 'Paul Greengrass')

[Pradeep1210]

10) Show the names of actors who have worked in movies with a rank in the top 10 inclusive.
Show the movie titles worked in and rank. Sort in descending order of rank.

SELECT TOP 10 actors.*, movies.title
FROM actors
INNER JOIN movies on movies.movieid=actors.movieid
INNER JOIN ratings on movies.movieid=ratings.movieid
ORDER BY ratings.rank DESC

[MoE70]

9) works great thanks

SELECT TOP 10 actors.*, movies.title
FROM actors
INNER JOIN movies on movies.movieid=actors.movieid
INNER JOIN ratings on movies.movieid=ratings.movieid
ORDER BY ratings.rank DESC

getting syntax errors here for Q10

mysql> SELECT TOP 4 actors.*, movies.title
-> FROM actors
-> INNER JOIN movies on movies.movieid=actors.movieid
-> INNER JOIN ratings on movies.movieid=ratings.movieid
-> ORDER BY ratings.rank DESC;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '4 act
ors.*, movies.title
FROM actors
INNER JOIN movies on movies.movieid=actors.' at line 1
mysql>

[Pradeep1210]

Try this:

SELECT TOP 10 actors.actorid, actors.name, actors.sex, movies.title
FROM actors
INNER JOIN movie2actors ON movie2actors.actorid=actors.actorid
INNER JOIN movies on movies.movieid=movie2actors.movieid
INNER JOIN ratings on movies.movieid=ratings.movieid
ORDER BY ratings.rank DESC

[MoE70]

Try this:

SELECT TOP 10 actors.actorid, actors.name, actors.sex, movies.title
FROM actors
INNER JOIN movie2actors ON movie2actors.actorid=actors.actorid
INNER JOIN movies on movies.movieid=movie2actors.movieid
INNER JOIN ratings on movies.movieid=ratings.movieid
ORDER BY ratings.rank DESC

that does work either. do you want to try it on my DB its pretty small heres the SQL attached to construct it