SQL Query Error

Question

SQL Query Error

goredema

SSC Journeyman

Points: 90
More actions
May 17, 2010 at 4:19 am

#219013

I want to list films with the actor who has received the most awards appeared
in?
SELECT Film.FilmID, FilmTitle
FROM Award INNER JOIN
Role ON Award.RoleID = Role.RoleID INNER JOIN
Film ON Role.FilmID = Film.FilmID INNER JOIN
Person ON Role.PersonID = Person.PersonID
WHERE Film.FilmID IN (
SELECT Award.AwardID
FROM Award INNER JOIN
Role ON Award.RoleID = Role.RoleID INNER JOIN
Film ON Role.FilmID = Film.FilmID INNER JOIN
Person ON Role.PersonID = Person.PersonID
WHERE Award.RoleID IN (
SELECT Award.RoleID
FROM Award INNER JOIN
Role ON Award.RoleID = Role.RoleID INNER JOIN
Film ON Role.FilmID = Film.FilmID INNER JOIN
Person ON Role.PersonID = Person.PersonID
Group by Award.AwardID, Award.RoleID
Having COUNT(Award.AwardID) = (SELECT MAX(COUNT(*) )
FROM Award INNER JOIN Role ON Award.RoleID = Role.RoleID )
)
)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

goredema SSC Journeyman Points: 90 More actions · Answer 2

Tables are

Genre(GnreID,GenreDescription)

RoleType(RoleTypeID, RoleTypeDescription)

Nationality(NationalityID, NationalityDescription)

Film(FilmID, FilmTitle,Release Date, GenreID, StudioID, FilRating, Plot)

Role(RoleID, RoleTypeID, PersonID)

Person(PersonID, FName, Sname, NationalityID, GenderID, GenderDescription)

Gender(GenderID, GDescrip)

Studio(StudioID, SType)

BoxOfficeTakings(TakingID, TimePeriodID,FilmID,TakingsMoney)

Award(AwardID, RoleID, AwardDEscription, AwardType, YearAwarded)

TimePeriod(TimePeriod, WeekNumber, WeekStartedDate, WeekEndDate, Year)

Region(RegionID, RegionName)

AwardType(AwardTypeID, AwardTypeDescription)

AwardCategory(AwardCategoryID, AwardTypeDescription)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

I'm going to assume you didn't read the article I listed. Please take a minute and do so. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass