Asad Mahmood Asad Mahmood - 7 days ago 8
SQL Question

Am I doing my JOINS incorrectly, Output not as expected?

I am trying to get a desired output as shown below however I am not sure why I am not getting it correctly. I don't know why I am getting movies such as "Date Night" or "Drive Angry" show up as they have no

MovieID
inside of tblReview. I thought I would have
Critic
and
User
on every line however for some reason I only have it on two lines for my output. My average's are also incorrect, however some of them use scientific notation.

In what ways can I fix these issues described above?

Script:

SELECT MovieTitle AS "MOVIE", TBLCRITICCLASS.CRITICCLASSDESC AS "Critic Type", AVG(TBLREVIEW.REVIEWSTAR) AS "Average Rating"
FROM TBLMOVIE
FULL JOIN TBLREVIEW ON TBLMOVIE.MOVIEID = TBLREVIEW.MOVIEID
FULL JOIN TBLCRITIC ON TBLREVIEW.CRITICID = TBLCRITIC.CRITICID
FULL JOIN TBLCRITICCLASS ON TBLCRITIC.CRITICID = TBLCRITICCLASS.CRITICCLASSID
group by MovieTitle, TBLCRITICCLASS.CRITICCLASSDESC
Order by Movietitle;


Output with script:

MOVIE Critic Type Average Rating
----------------------------------- ------------------------- ---------------------------------------
30 Minutes or Less 3.8E+00
A Lonely Place to Die Critic 9
A Lonely Place to Die 8.7E+00
Abraham Lincoln: Vampire Hunter
Act of Valor
Captain America: The First Avenger
Date Night
Drive Angry
Saving Private Ryan
Taken User 6
Taken 6.9375
Taken 2 5.6


13 rows selected


Expected output:

MOVIE Critic Type Average Rating
------------------------ ------------ --------------
30 Minutes or Less Critic 2.00
30 Minutes or Less User 4.20
A Lonely Place to Die Critic 10.00
A Lonely Place to Die User 8.50
Taken Critic 6.17
Taken User 7.27
Taken 2 Critic 4.00
Taken 2 User 6.29

8 rows selected


Here are the files associated:

File1 (Pastebin)

File 2 (Pastebin)

Answer

Start with INNER JOIN:

SELECT MovieTitle AS "MOVIE", TBLCRITICCLASS.CRITICCLASSDESC AS "Critic Type", AVG(TBLREVIEW.REVIEWSTAR) AS "Average Rating"
FROM TBLMOVIE INNER JOIN
     TBLREVIEW
     ON TBLMOVIE.MOVIEID = TBLREVIEW.MOVIEID INNER JOIN
     TBLCRITIC
     ON TBLREVIEW.CRITICID = TBLCRITIC.CRITICID INNER JOIN
     TBLCRITICCLASS
     ON TBLCRITIC.CRITICID = TBLCRITICCLASS.CRITICCLASSID 
group by MovieTitle, TBLCRITICCLASS.CRITICCLASSDESC
Order by Movietitle;

Your database should have proper foreign key relationships and you seem to want rows that match in the different tables.

If this doesn't work, then check the JOIN conditions. For instance, this doesn't look right: TBLCRITIC.CRITICID = TBLCRITICCLASS.CRITICCLASSID. However, you don't describe the tables, so it is not possible to determine what the right conditions are.

Comments