When I run this query, why are other actors getting values that are specific to other actors?

Question

DROP DATABASE IF EXISTS ACTOR;
CREATE DATABASE ACTOR;
USE ACTOR;

CREATE TABLE IF NOT EXISTS actor(
    actorID INT PRIMARY KEY,
    birth_name VARCHAR(50) NOT NULL,
    age INT,
    reside VARCHAR(45) NOT NULL,
    partner VARCHAR(45)
    );

CREATE TABLE IF NOT EXISTS movie(
    movieID INT PRIMARY KEY,
    movie_name VARCHAR(50) NOT NULL,
    year_made INT
);

CREATE TABLE IF NOT EXISTS tag(
    tagID INT PRIMARY KEY,
    tag_list VARCHAR(50) NOT NULL,
    actorID INT REFERENCES actor(actorID)
);

CREATE TABLE IF NOT EXISTS rating(
    ratingID INT PRIMARY KEY,
    actorID INT REFERENCES actor(actorID),
    movieID INT REFERENCES movie(movieID),
    actor_rating DECIMAL(3,1) NOT NULL,
    movie_rating DECIMAL(3,1) NOT NULL
);


INSERT INTO actor VALUES
    (1,'Benjamin Géza Affleck-Boldt',47,'US','Kelleth Cuthbert'),
    (2,'Caleb Casey Affleck',44,'US','Floriana Lima'),
    (3,'Kevin Norwood Bacon',61,'US','Kyra Sedgwick'),
    (4,'Alexander Rae Baldwin III',61,'US','Hilaria Baldwin'),
    (5,'Jason Kent Bateman',50,'US','Amanda Anka');

INSERT INTO movie VALUES
    (11,'Armageddon',1998),
    (12,'American Pie',1999),
    (13,'Apollo 13',1995),
    (14,'The Departed',2006),
    (15,'Hancock',2008)
;

INSERT INTO tag VALUES
    (21,'actor, american, director',1),
    (22,'actor, american, talented',2),
    (23,'actor, american, musician',3),
    (24,'actor, american, funny',4),
    (25,'actor, american, Cute',5)
;

INSERT INTO rating VALUES
    (51,1,11,6.2, 6.2),
    (52,2,12,6.7, 6.1),
    (53,3,13,7.1, 7.0),
    (54,4,14,6.6, 8.0),
    (55,5,15,7.2, 5.9)
    ;


SELECT birth_name, actor_rating
FROM actor, rating
WHERE age IN(61)
;

Im trying to return the birth name and the actors rating if their age is 61

query result

Kevin Norwood Bacon     6.2
Alexander Rae Baldwin   6.2
Kevin Norwood Bacon     6.7
Alexander Rae Baldwin   6.7
Kevin Norwood Bacon     7.1
Alexander Rae Baldwin   7.1
Kevin Norwood Bacon     6.6
Alexander Rae Baldwin   6.6
Kevin Norwood Bacon     7.2
Alexander Rae Baldwin   7.2

how would I fix this to not have each actor get values pertaining to other actors? I have looked at my screen trying to dissect the problem for such a simple query, but I still don't know what's causing this. could it be that my code is wrong, or my query? I am in a beginner class and am just starting off with simple queries, so stick to that would be appreciated thank you.

Remarkable https://stackoverflow.com/questions/58988703/when-i-run-this-simple-query-the-values-of-each-table-is-being-repeated-and-i - are you in the same class? — P.Salmon, Nov 23 '19 at 07:41

score 0 · Answer 1 · answered Nov 23 '19 at 02:51

0

Try with this query:

SELECT birth_name, actor_rating
FROM actor, rating
WHERE age IN(61) and rating.actorID = actor.actorID

answered Nov 23 '19 at 02:51

zip

3,938
2
11
19

1

you should use join and not comma in your querys. Its antiquated style – nbk Nov 23 '19 at 03:07

score 0 · Answer 2 · answered Nov 23 '19 at 05:09

0

You can try this with join

SELECT birth_name, actor_rating 
FROM actor 
INNER JOIN rating 
ON actor.actorID=rating.actorID 
WHERE age IN(61)

Result should like this

answered Nov 23 '19 at 05:09

Vinay Kaklotar

424
4
11

When I run this query, why are other actors getting values that are specific to other actors?

Im trying to return the birth name and the actors rating if their age is 61

query result

2 Answers2