SQL Query prints 4 times/Advice

Question

I am getting repeating rows when I query this table using the below query. I am not sure why this is happening, could someone please help explain?

I get 4 repeating rows when I query the data when the expected result should be only 1 row.

The query is:

SELECT d.DIRECTOR_FNAME, d.Director_lname, s.studio_name
FROM DIRECTOR d, STUDIO s, FILM f, CASTING c
WHERE s.STUDIO_ID = f.STUDIO_ID
AND f.FILM_ID = c.FILM_ID
AND d.DIRECTOR_ID = c.DIRECTOR_ID
AND f.FILM_TITLE = 'The Wolf Of Wall Street';

And here's the table, I probably didn't need to put the entire table in but it's done now.

drop table casting;
drop table film;
drop table studio;
drop table actor;
drop table director;

CREATE TABLE studio(
studio_ID NUMBER NOT NULL,
studio_Name VARCHAR2(30),
PRIMARY KEY(studio_ID));

CREATE TABLE film(
film_ID NUMBER NOT NULL,
studio_ID NUMBER NOT NULL,
genre VARCHAR2(30),
genre_ID NUMBER(1),
film_Len NUMBER(3),
film_Title VARCHAR2(30) NOT NULL,
year_Released NUMBER NOT NULL,
PRIMARY KEY(film_ID),
FOREIGN KEY (studio_ID) REFERENCES studio);

CREATE TABLE director(
director_ID NUMBER NOT NULL,
director_fname VARCHAR2(30),
director_lname VARCHAR2(30),
PRIMARY KEY(director_ID));

CREATE TABLE actor(
actor_ID NUMBER NOT NULL,
actor_fname VARCHAR2(15),
actor_lname VARCHAR2(15),
PRIMARY KEY(actor_ID));

CREATE TABLE casting(
film_ID NUMBER NOT NULL,
actor_ID NUMBER NOT NULL,
director_ID NUMBER NOT NULL,
PRIMARY KEY(film_ID, actor_ID, director_ID),
FOREIGN KEY(director_ID) REFERENCES director(director_ID),
FOREIGN KEY(film_ID) REFERENCES film(film_ID),
FOREIGN KEY(actor_ID) REFERENCES actor(actor_ID));

INSERT INTO studio (studio_ID, studio_Name) VALUES (1, 'Paramount');
INSERT INTO studio (studio_ID, studio_Name) VALUES (2, 'Warner Bros');
INSERT INTO studio (studio_ID, studio_Name) VALUES (3, 'Film4');
INSERT INTO studio (studio_ID, studio_Name) VALUES (4, 'Working Title Films');

INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (1, 1, 'Comedy', 1, 180, 'The Wolf Of Wall Street', 2013);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (2, 2, 'Romance', 2, 143, 'The Great Gatsby', 2013);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (3, 3, 'Science Fiction', 3, 103, 'Never Let Me Go', 2008);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (4, 4, 'Romance', 4, 127, 'Pride and Prejudice', 2005);

INSERT INTO director (director_ID, director_fname, director_lname) VALUES (1, 'Martin', 'Scorcese');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (2, 'Baz', 'Luhrmann');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (3, 'Mark', 'Romanek');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (4, 'Joe', 'Wright');

INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (1, 'Matthew', 'McConnaughy');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (2, 'Leonardo', 'DiCaprio');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (3, 'Margot', 'Robbie');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (4, 'Joanna', 'Lumley');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (5, 'Carey', 'Mulligan');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (6, 'Tobey', 'Maguire');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (7, 'Joel', 'Edgerton');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (8, 'Keira', 'Knightly');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (9, 'Andrew', 'Garfield');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (10, 'Sally', 'Hawkins');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (11, 'Judi', 'Dench');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (12, 'Matthew', 'Macfadyen');

INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 1, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 2, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 3, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 4, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 2, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 5, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 6, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 7, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 5, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 8, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 9, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 10, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 5, 4);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 8, 4);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 11, 4);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 12, 4);

This is not [tag:mysql]. MySQL doesn't know NUMBER nor VARCHAR2 but thanks for posting an otherwise usable example. Too many people just ask “I get 4 rows. Whuzz wrong?” ;-) — PerlDuck, Mar 18 '16 at 21:25

score 1 · Accepted Answer · answered Mar 18 '16 at 20:44

First question you should be asking is "What Result Set Am I Looking For?". Your query is returning The Director First Name, Director Last Name, and Studio Name where the Film Title is "The Wolf of Wall Street", for each cast that belongs to that Film. You get 4 records because you get a record for each casting where FilmId = 1 in this case. Include the ActorId column and you will see what I am talking about. Hope that helps... if not I would study harder earlier.

I split casting up into two tables, film_actor & film_director — Poena, Mar 20 '16 at 05:35

Shachaf.Gortler · Answer 2 · 2016-03-18T21:07:52.173

0

You are missing a join, instead of an AND , follow this example :

SELECT d.DIRECTOR_FNAME, d.Director_lname, s.studio_name
FROM   DIRECTOR d
INNER  JOIN STUDIO  s ON s.STUDIO_ID = f.STUDIO_ID
INNER  JOIN CASTING c ON d.DIRECTOR_ID = c.DIRECTOR_ID
INNER  JOIN FILM    f ON f.FILM_ID = c.FILM_ID
WHERE f.FILM_TITLE = 'The Wolf Of Wall Street';

edited Mar 18 '16 at 21:07

answered Mar 18 '16 at 20:27

Shachaf.Gortler

5,655
14
43
71

I've tried to get that working with no luck I'm not sure what exactly the syntax of that query should look like. – Poena Mar 18 '16 at 20:52
He's missing the ON keyword: Should be `... inner join studio s ON s.studio_id=f.studio_id...` etc. – PerlDuck Mar 18 '16 at 20:54
Well, almost ;-) Your version avoids the (bad) comma-join, but the results are the same. Your version should be preferred though; see http://stackoverflow.com/q/1018822/5830574. I think @Bradley pinpointed the problem: It's the four actors. – PerlDuck Mar 18 '16 at 21:12
it says "ORA-00904: "F"."STUDIO_ID": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 91 Column: 38" – Poena Mar 18 '16 at 21:58
@Jordan5497 I guess (but am not sure) this is because the first ON clause `s.STUDIO_ID = f.STUDIO_ID` refers to table `f` which is not yet mentioned. Try reordering the JOIN…ON clauses to DIRECTOR → CASTING → FILM → STUDIO, i.e. simply shuffle the three lines beginning with `INNER JOIN…` in the answer's code. – PerlDuck Mar 19 '16 at 15:09

score 0 · Answer 3 · answered Mar 18 '16 at 20:42

0

In order to prevent duplication of query results just add DISTINCT after SELECT. This is how it should look like:

SELECT DISTINCT d.DIRECTOR_FNAME, d.Director_lname, s.studio_name
FROM DIRECTOR d, STUDIO s, FILM f, CASTING c
WHERE s.STUDIO_ID = f.STUDIO_ID
AND f.FILM_ID = c.FILM_ID
AND d.DIRECTOR_ID = c.DIRECTOR_ID
AND f.FILM_TITLE = 'The Wolf Of Wall Street';

Likewise, in the table casting you do not have a proper Primary Key, please, add something like CastingId. In the same way, try not to write Id's by hand, instead increment them automatically.

answered Mar 18 '16 at 20:42

Coke

965
2
9
22

Thanks for the advice on the castingID and your query worked perfectly. I remember using Distinct briefly in a lab before. – Poena Mar 18 '16 at 20:49
1

I always feel uncomfortable with `distinct` because (to my experience) it only wipes out duplicates _because someone missed a condition_. (And No, I wasn't the downvoter.) – PerlDuck Mar 18 '16 at 21:19
1

Perl Dog, thanks for being honest. In fact, you are right, it will not solve the problem and you will still get the duplication, however it will be hidden. To experience better quality, the query itself must be meaningful and accurate. Actually, the core solution is to construct a good query. – Coke Mar 19 '16 at 09:25

SQL Query prints 4 times/Advice

3 Answers3