SELECT COMMAND SQL more than 1 value

Question

Im trying to create a select command to query the database I have and find different values from different tables in the database.

This is what I have tried: SELECT Movie.title, Movie.year, Actor.firstName, Actor.lastName, Role.roleName From Movie, Actor, Role WHERE quoteID='Houston, we have a problem.'; / — user1300580
– user1300580, Commented Mar 29, 2012 at 12:09
Which table are quotes stored in? The answer from @Dor Cohen looks like the right approach but you haven't said where to find a quote for a movie — kaj
– kaj, Commented Mar 29, 2012 at 12:10
the quote from the movie comes from the Quote table and is listed as quoteCHAR — user1300580
– user1300580, Commented Mar 29, 2012 at 12:18

Dor Cohen · Accepted Answer · 2012-03-29 14:50:02Z

3

Try the following, I change all ID columns from CHAR to NUMBER

CREATE TABLE Actor
(actorID NUMBER,
lastName CHAR(24),
firstName CHAR(24),
middleName CHAR(24),
suffix CHAR(6),
gender CHAR(1),
birthDate DATE,
deathDate DATE)
/

CREATE TABLE Movie
(movieID NUMBER,
title CHAR(36),
year NUMBER,
company CHAR(50),
totalNoms NUMBER,
awardsWon NUMBER,
DVDPrice NUMBER(5,2),
discountPrice NUMBER(5,2))
/

CREATE TABLE Role
(roleID NUMER,
roleName CHAR(36),
gender CHAR(1),
actorID NUMBER,
movieID NUMBER)
/

CREATE TABLE Quote
(quoteID NUMBER,
quoteCHAR CHAR(255))
/

CREATE TABLE RoleQuote
(roleID NUMBER,
quoteID NUMBER)
/

and the select will be:

SELECT  Movie.Title , Movie.Year , Actor.Firstname, Actor.lastname, Role.roleName, Quote.quoteCHAR 
FROM    Movie, Actor, Role, Quote, RoleQuote
WHERE   Movie.movieID = Role.movieId AND 
    Actor.actorID = Role.ActorId AND 
    Role.roleID = RoleQuote.roleID AND
    Quote.quoteID = RoleQuote.quoteID AND
    Quote.quoteCHAR LIKE '%Houston, we have a problem.%'

edited Mar 29, 2012 at 14:50

answered Mar 29, 2012 at 12:03

Dor Cohen

17.1k24 gold badges97 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mike Sherrill 'Cat Recall' Over a year ago

I'd hope schools are teaching INNER JOIN syntax by now. It's 2012, after all.

user1300580 Over a year ago

This is what I have been working on no idea if its close but from what I am aware I am sure it is not necessary to add the two tables you are suggesting: SELECT Movie.title, Movie.year, Actor.firstName, Actor.lastName, Role.roleName FROM Movie, Actor, Role WHERE Quote.quoteCHAR ='Houston, we have a problem.'; /

Dor Cohen Over a year ago

How you connect between movie and Actor? is there only one movie per actor?

user1300580 Over a year ago

SELECT Movie.title, Movie.year, Actor.firstName, Actor.lastName, Quote.quoteCHAR, Role.roleName FROM Movie, Actor, Quote, Role WHERE Quote.quoteCHAR ='Houston, we have a problem.'; / when I use the above it makes it go crazy and doesnt stop listing things

Dor Cohen Over a year ago

It's because you have Cartesian product you have to perform JOIN on this table by related columnms

|

Collectives™ on Stack Overflow

SELECT COMMAND SQL more than 1 value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related