SQL issue, please assist

Question

I'd like to know how this would be set out in SQL - "Which directors directed more than one film, and the movie was nominated for more than 2 Academy Awards. Order results by Academy Awards descending".

I currently have this..which I know is incorrect.

SELECT DIRECTOR.dirID, MOVIE.mvTitle
FROM DIRECTOR
INNER JOIN
MOVIE ON DIRECTOR.dirID=MOVIE.dirID
ORDER BY noms>=2 DESC;

`SELECT director.dirID, movie.mvTiltle FROM director INNER JOIN movie ON director.dirID = movie.dirID where director.numOfMovies > 1 and movie.nom >= 2 ORDER BY movie.nom DESC` — bayblade567, Mar 29 '15 at 06:36
"Which directors directed more than one film, and the movie was nominated for more than 2 Academy Awards. Order results by Academy Awards descending" - this is confusing me. Are you asking for any director that directed more than one film and return any movie with more than 2 nominations he/she's directed? — Yatrix, Mar 29 '15 at 07:14
Give as scheme of your tables. HINT: use having(). It's hard to write complete solution without full table's info. — Alexander R., Mar 29 '15 at 07:29
Yes, this is quite confusing do blame my idiotic "teachers". From what I understood it's asking for a list of directors who has directed more than ONE movie(At least 2). The 2(+more) movies they directed must also have been nominated at least twice. Meaning we're looking for a director who's directed 2+ movies that have at least been nominated 2+ times. This was one of the reasons why I'm struggling with SQL, misleading questions and hardly any proper knowledge on SQL then tossed with a 16Page assignment limited with 2weeks. — Potato Sloth, Mar 29 '15 at 07:30
Microsoft Access, though I'm lacking tons of information on it. — Potato Sloth, Mar 29 '15 at 10:03

bayblade567 · Answer 1 · 2015-03-29T09:56:01.953

0

You didn't add the where clause to check for the conditions, the query would be:

SELECT director.dirID, movie.mvTiltle 
FROM director 
INNER JOIN movie ON director.dirID = movie.dirID 
WHERE director.numOfMovies > 1 
    and movie.nom >= 2 
ORDER BY noms DESC

edited Mar 29 '15 at 09:56

answered Mar 29 '15 at 06:37

bayblade567

270
1
10

2

Who told you he have director.numOfMovies and movie.nom fields? – Alexander R. Mar 29 '15 at 07:27
I added director.numOfMovies for this **directors directed more than one film** and I **noms** was specified in the question itself. and I changed movies.nom to nom, it was a mistake. – bayblade567 Mar 29 '15 at 09:55

sqluser · Answer 2 · 2015-03-29T06:55:36.460

0

You need to use WHERE clause

SELECT DIRECTOR.dirID, MOVIE.mvTitle
FROM DIRECTOR
INNER JOIN MOVIE ON DIRECTOR.dirID = MOVIE.dirID
WHERE DIRECTOR.dirID > 1
AND MOVIE.noms > 2

edited Mar 29 '15 at 06:55

answered Mar 29 '15 at 06:39

sqluser

5,502
7
36
50

score 0 · Answer 3 · answered Mar 29 '15 at 07:56

0

;with cte as (select d.dirid
    from movie m
    inner join director d on m.dirid=d.dirid
    where noms >1
    group by d.dirid
    having count(*)>1)
select cte.dirid,m.name,nos from cte 
inner join movie m on cte.dirid=m.dirid
order by noms desc;

Something like this should work. Here is the fiddle: http://sqlfiddle.com/#!6/9db91/1

answered Mar 29 '15 at 07:56

Yatrix

13,361
16
48
78

The `;` is used to **end** a statement in (standard) SQL. It's not used to at the beginning of a statement. And if you think that you need it in T-SQL, please read this: http://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon – Mar 29 '15 at 10:11
Yes, I know that. http://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon – Yatrix Mar 29 '15 at 14:30

SQL issue, please assist

3 Answers3