A difficult SQL statement ... Four tables?

Question

I have compiled a database which, I believe, follows all the “correct” ways of working (no redundant data, etc.), as shown below:

I want to display the data stored in the Nomination table, but with a specific EventID, as well as with the name of the film and the year and name of the award. I know the basic associations, but it seems rather complicated - because I'm a beginner. Any help on the right way to approach this problem, so that I may run into more complex ones in the future, is greatly appreciated!

Answer 1

The study is joining. This is an important part of using SQL. When you have a select statement that works, examine the “views” and create a new view using the select statement.

To get you started, this is an unverified, from my point of view, example of a select expression using joins. Also note that MySQL is not a tool that I often use, so the syntax is probably incorrect for MySQL.

SELECT A.Title, A.Year, C.Name, D.EventID FROM FILMS A JOIN NOMINATIONS B ON B.FilmID = A.FilmID JOIN AWARDS C ON C.AwardID = B.AwardID JOIN EVENTS D ON D.EventID = B.EventID WHERE EVENTS.EventID = XX 

This will work if all relationships exist. Examine the "outer join" to pull out all the rows of a given table, say, FILMS, and include the corresponding row in other tables.

An author named Joe Selco has written several books on complex SQL statements. You may find his work useful.

Hth

Answer 2

This is definitely the right way to structure this data, as it will allow you to summarize your data in a rather concise way. In fact, the query you are looking for is a simple series of joins:

 SELECT n.*, f.Year, f.Title, a.Name FROM `nominations` n INNER JOIN `events` e ON n.EventID = e.EventID INNER JOIN `films` f ON n.FilmID = f.FilmID INNER JOIN `awards` a ON n.AwardID = a.AwardID WHERE n.EventID = ? 

In fact, you don’t even need to join the Events table if you do not want to retrieve data from the Ceremonies table.

Answer 3

First, execute your request using the first qualifier, and then build it ... You need a specific type of "Event ID", go there. Since events and ceremonies are more likely “lookup tables,” you probably need all the qualification nominations, so I'm really going to start there.

 select JustNominees.*, f.Title, f.Year as FilmYear, a.Name as AwardName from ( select n.*, e.Name as EventName, c.Year as CeremonyYear from Nominations n join Events e on n.EventID = e.EventID join Ceremonies c on e.CeremonyID = c.CeremonyID where e.EventID = WhatEventID ) JustNominees join Films f on JustNominees.FilmID = f.FilmID join Awards a on JustNominees.AwardID = a.AwardID 

Answer 4

This is actually a basic connection, so you should already know this :-)

First select the correct nominations , and then attach the rest to it:

 SELECT nominations.nominationid, films.title, films.year, awards.name FROM nominations JOIN films ON films.filmid = nominations.filmid JOIN awards ON awards.awardid = nominations.awardid WHERE nominations.eventid = :event_id