July 15, 2008 at 3:06 pm
here are the 3 databases
movie
Field name Type Notes
id INTEGER An arbitrary unique identifier
title CHAR(70) The name of the film - usually in the language of the first release.
yr DECIMAL(4) Year of first release.
score FLOAT Average of all the votes cast for the film. (Internet users can vote for films on a scale of 1-10)
votes INTEGER The number of votes cast for this film.
actor
Field name Type Notes
id INTEGER An arbitrary unique identifier
name CHAR(36) The name of the actor (the term actor is used to refer to both male and female thesps.)
casting
Field name Type Notes
movieid INTEGER A reference to the movie table.
actorid INTEGER A reference to the actor table.
ord INTEGER The ordinal position of the actor in the cast list. The star of the movie will have ord value 1 the co-star will have value 2, ...
How can i get the result for this?
query to Obtain the cast list for the film 'Alien'
thanks much
July 15, 2008 at 3:08 pm
You're going to be querying from a join between the tables. You say, "3 databases", does that mean these are in separate databases, or are they separate tables in the same database?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2008 at 3:11 pm
sorry about that , i should have said 3 tables.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
July 15, 2008 at 3:19 pm
You'll need something like this:
select [Name]
from dbo.Actor
inner join dbo.Casting
on Actor.ID = Casting.ActorID
inner join dbo.Movie
on Casting.MovieID = Movie.ID
where Title = 'Alien'
That won't sort the actors. You'll need an Order By clause for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2008 at 3:34 pm
thanks much that helped..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply