CODEWITHSUNDEEP
mysqlsqldatabasesyntax-error
Brett Saguid
Brett Saguid

Reputation: 13

SQL Query - Select Query

Here are my database relations:

shows(showID, title, premiere_year, network, creator, category)

episode(showID, episodeID, airdate, title)

actor(actID, fname, lname)

main_cast(showID, actID, role)

recurring_cast(showID, episodeID, actID, role)

customer(custID, fname, lname, email, creditcard,membersince,renewaldate, password, username)

cust_queue(custID, showID, datequeued)

watched(custID, showID, episodeID, datewatched)

All the 'IDs' are primary keys

I have queries I was given and for some, I don't know how to go about it. Such as:

Find all actors who are in the main cast of at least one show and in the recurring cast of at least one show. Display the actor's first name, last name, the title of the show in which the actor is in the main cast, the title of the show in which the actors is in the recurring cast, and the role the actor plays in each show.

I'm trying:

{
    SELECT Actor.fname, Actor.lname, Shows.Title
    FROM Actor, Shows, Main_Cast, Recurring_Cast
    WHERE Actor.actID = Main_Cast.actID AND Actor.actID = Recurring_Cast.actID;
}

But I don't think that's right. Any ideas??

Upvotes: 0

Views: 176

Answers (1)

yiabiten
yiabiten

Reputation: 810

Try the following:

SELECT Actor.fname, Actor.lname, Shows.Title
FROM Actor, Shows  AS sh
WHERE Actor.actID 
IN 
      (SELECT actID FROM Main_Cast
      WHERE sh.showID==Main_Cast.showID)
UNION
      (SELECT actID FROM Recurring_Cast
      WHERE sh.showID==Recurring_Cast.showID)

This will show you the actors who are in the main cast and recurring cast of the same show. You can edit it a little to get what you want.

Upvotes: 1

Related Questions