CODEWITHSUNDEEP
sqldatabasems-accesssubquery
haley
haley

Reputation: 1

Convert subquery to a standard query using INNER JOIN syntax

I am using an Access Database and need help converting the following nested query into an inner join query.

The DIRNUM is the primary key in the DIRECTOR table and it is also in the MOVIE table. The MVNUM is the primary key in the MOVIE table and MOVSTAR table. 

SELECT dirname 
FROM director
WHERE dirnum IN (SELECT dirnum 
                 FROM movie 
                 WHERE mvnum IN  (SELECT mvnum 
                                  FROM movstar 
                                  WHERE starnum=2)
                );

Upvotes: 0

Views: 871

Answers (2)

Stan Shaw
Stan Shaw

Reputation: 3034

You want to select distinct (since you aren't using "WHERE IN" syntax, you will get duplicates)

SELECT DISTINCT dirname 
FROM 
    (director d
INNER JOIN 
    movie m ON m.dirnum = d.dirnum)
INNER JOIN
    movstar ms ON ms.mvnum = m.mvnum
WHERE
    ms.starnum=2

Upvotes: 2

Felipe Deguchi
Felipe Deguchi

Reputation: 586

SELECT
    dirname 
FROM 
    (director d
INNER JOIN 
    movie m ON m.dirnum = d.dirnum)
INNER JOIN
    movstar ms ON ms.mvnum = m.mvnum
WHERE
    ms.starnum=2

EDIT: Altered to add parenthesis as pointed out by HansUp

Upvotes: 1

Related Questions