Reputation: 11
Q; The Shahrukh number of an actor is the length of the shortest path between the actor and Shahrukh Khan in the "co-acting" graph. That is, Shahrukh Khan has Shahrukh number 0; all actors who acted in the same film as Shahrukh have Shahrukh number 1; all actors who acted in the same film as some actor with Shahrukh number 1 have Shahrukh number 2, etc. Return all actors whose Shahrukh number is 2
query9 ="""WITH SHAHRUKH_0
AS
(
SELECT
TRIM(P.PID) PID
FROM
Person P
WHERE
Trim(P.NAME) = "Shah Rukh Khan"
)
, SHAHRUKH_1_MOVIES
AS
(
SELECT DISTINCT
TRIM(MC.MID) MID
, S0.PID
FROM
M_Cast MC
, SHAHRUKH_0 S0
WHERE
TRIM(MC.PID) = S0.PID
)
, SHAHRUKH_1_ACTORS
AS
(
SELECT DISTINCT
TRIM(MC.PID) PID
FROM
M_Cast MC
, SHAHRUKH_1_MOVIES S1M
WHERE
TRIM(MC.MID) = S1M.MID
AND TRIM(MC.PID) <> S1M.PID
)
, SHAHRUKH_2_MOVIES
AS
(
SELECT DISTINCT
TRIM(MC.MID) MID
, S1A.PID
FROM
M_Cast MC
, SHAHRUKH_1_ACTORS S1A
WHERE
TRIM(MC.PID) = S1A.PID
)
SELECT DISTINCT
TRIM(P.Name) ACTOR_NAME
FROM
Person P
, M_Cast MC
, SHAHRUKH_2_MOVIES S2M
WHERE
TRIM(MC.PID) = TRIM(P.PID)
AND TRIM(MC.MID) = S2M.MID
AND TRIM(MC.PID) != S2M.PID;"""
Total s2 actors are 25698 but i am getting 26521. please suggest.
Upvotes: 0
Views: 488