CODEWITHSUNDEEP
sqlpostgresql
Hellllp
Hellllp

Reputation: 45

Find list of actors and movies they acted in and if they have not acted make the movie title null

So i need to simply return all of the actors along with the movies they acted in and I cannot use outerjoin which would make this all a lot easier..

Here is the database:

CREATE TABLE actors(
AID INT,
name VARCHAR(30) NOT NULL,
PRIMARY KEY(AID));

CREATE TABLE movies(
    MID INT,
    title VARCHAR(30),
    PRIMARY KEY(MID));

CREATE TABLE actor_role(
    MID INT,
    AID INT,
    rolename VARCHAR(30) NOT NULL,
    PRIMARY KEY (MID,AID),
    FOREIGN KEY(MID) REFERENCES movies,
    FOREIGN KEY(AID) REFERENCES actors);

and this is what I have tried and it returns everything I need besides the actors that have not acted in a movie, 

SELECT name,title 
FROM actors, actor_role, movies
where actors.AID = actor_role.AID and movies.mid = actor_role.mid;

I simply need to change the above SQL to include actors that have not acted in any movie and do so without using SQL OUTER JOIN syntax. Any help will be appreciated.

Upvotes: 1

Views: 755

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Well you can UNION ALL the actors where no corresponding row in actor_role exits.

SELECT a.name,
       m.title 
       FROM actors a
            INNER JOIN actor_role ac
                       ON a.aid = ac.aid
            INNER JOIN movies m
                       ON m.mid = ac.mid
UNION ALL
SELECT a.name,
       NULL
       FROM actors a
       WHERE NOT EXISTS (SELECT *
                                FROM actor_role ac
                                WHERE ac.aid = a.aid;

I also recommend to use explicit JOIN syntax over the implicit one.

Upvotes: 1

Related Questions