Unique numbers & names of stars that have appeared in at least one comedy

Question

I have a database with several tables.

Film (filmID, title, filmCatagory)
FilmCast (filmID, filmStarID, filmStarName)
FilmStar (filmStarID, filmStarName, birthplace).

The Film entity states all information about the films, FilmCast links the two tables together as a way to show which film star stars in what particular film, FilmStar states information about all of the stars. I need to translate the following question into an SQL query:

List the unique numbers and names of all stars that have appeared in at least one comedy.

I understand that a join will be used, but am unsure how this query will work with the three tables.

You likely don't want filmCast filmStarName, as with the film title, since it must be kept in step with its value elsewhere. Unless it means the name they used just in that film. Ie better than the indirect/partial 'states information about' is *the parameterized statement that a row makes into a statement*. Eg 'film [filmID] stars star [filmStarID] named [filmStarName]'. (A table holds the rows making true statements.) Then a JOIN's meaning/predicate is the AND of its arguments' meanings/predicates! And a UNION's is the OR, etc. [Thus do we query.](http://stackoverflow.com/a/27682724/3404097) — philipxy, Oct 09 '15 at 23:57
@EitanK You don't need constraints to query, you just need to know what the tables/rows mean. — philipxy, Oct 10 '15 at 00:03
PS Which "name"? A star's name in filmStar? In filmCast? In their filmCast comedies? You haven't made it clear what you rows you want in the query. (Per my last comment.) (Ie its meaning or *predicate*.) (And per my previous comment you haven't made it clear what rows you want in the base tables.) — philipxy, Oct 10 '15 at 23:40

Wil Wang · Answer 1 · 2015-10-08T21:49:02.023

2

Find all of the film stars who have appeared in comedies and then GROUP BY.

SELECT s.filmStarID, s.filmStarName, COUNT(*)
FROM FilmStar s
INNER JOIN FilmCast c ON c.filmStarID = s.filmStarID
INNER JOIN Film f ON f.filmID = c.filmID
WHERE f.filmCategory = 'Comedy'
GROUP BY s.filmStarID, s.filmStarName

edited Oct 08 '15 at 21:49

answered Oct 08 '15 at 21:25

Wil Wang

74
3

1

`HAVING Count(*) > 0` - "at least one .." – stuartd Oct 08 '15 at 21:26
2

Well, the INNER JOIN would essentially filter out any stars who are not in comedies, so you can assume that any star that gets returned must have been in at least 1 comedy. – Wil Wang Oct 08 '15 at 21:28
Thank you very much for that. I am receiving the following error: ORA-00942: table or view does not exist – Tom Oct 08 '15 at 21:33
Right, WHERE specifies the filter criteria. In the query above since we have the films filtered for "comedy" only, the INNER JOIN would only return any records of Stars that have been in a comedy film. What I'm getting at is that the HAVING clause is unneeded since we have already filtered the Stars for comedy films, so that the "at least one" criteria is already fulfilled. Now if we started query with Films as the root and LEFT JOINED the other tables, we would need the HAVING clause. – Wil Wang Oct 08 '15 at 21:34
I am getting the following error now (the previous one was my mistake): ORA-00918: column ambiguously defined – Tom Oct 08 '15 at 21:37
@Tom oh right - you need to GROUP BY and SELECT using the table aliases. SELECT s.filmStarID, s.filmStarName ... GROUP BY s.filmStarID, s.filmStarName – Wil Wang Oct 08 '15 at 21:39
@tom, typo? Looking back at my query, I see that I misspelled c.filmStarID with c.filmStartID. I edited by answer to fix the typo issues – Wil Wang Oct 08 '15 at 21:48
No I noticed that before and the error still occurs – Tom Oct 08 '15 at 21:50
hmmm, are you returning any other fields? the ambiguous column message usually means that your projection has multiple same named columns. – Wil Wang Oct 08 '15 at 21:53
@Tom are you using Oracle or MySQL? The ORA error is from Oracle – fthiella Oct 08 '15 at 22:12
@WilWang yeah it means that the database doesn't no which table to get the data from because they have the same fields which is why you have the WHERE statement, i.e. WHERE FilmCastMember.filmID= Film.filmID – Tom Oct 08 '15 at 22:17

score 0 · Accepted Answer · answered Oct 08 '15 at 21:31

You could use a SELECT query with EXISTS:

SELECT
  filmStarID,
  filmStarName,
  birthplace
FROM
  FilmStar
WHERE
  EXISTS (
    SELECT *
    FROM
      Film INNER JOIN FilmCast
      ON Film.FilmID = FilmCast.FilmID
    WHERE
      Film.filmCatagory='Comedy'
      AND FilmCast.filmStarID = FilmStar.filmStarID
  )

score 0 · Answer 3 · answered Oct 08 '15 at 21:31

0

Have you tried something along the lines of

SELECT DISTINCT(filmStarID), filmStarName FROM FilmStar 
INNER JOIN FilmCast ON FilmStar.filmStarID = FilmCast.filmStarID 
INNER JOIN Film ON FilmCast.filmID = Film.filmID 
WHERE filmCategory = ‘Comedy’;

The idea of the join is to make one big table in memory that contains all of the data you need. You want to get the filmStarID and the filmStarName out based on Category, but you can't connect the FilmStar table directly to the Film category (they have no columns in common), so you join each of them to the FilmCast table, which has a column in common with each.

answered Oct 08 '15 at 21:31

Deric Miller

92
4

Thank you, I am receiving the following error: ORA-00911: invalid character – Tom Oct 08 '15 at 21:34
1

@Tom & Deric Miller 1.The first quote mark around Comedy is not an apostrophe. 2. The parentheses are unnecessary and misleading. (There's no function call.) 3. The select clause should have *`table.column`*. 4. @Tom This is *clearly* the simplest answer to the question. If filmCast filmStarName means the same thing as filmStar filmStarName then you don't even need to join with filmStar. (See my important still-unanswered comment on the question.) – philipxy Oct 10 '15 at 23:44

Unique numbers & names of stars that have appeared in at least one comedy

3 Answers3