0

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.

eshirvana
  • 23,227
  • 3
  • 22
  • 38
Mahesh Patil
  • 11
  • please provide sample data and desired output – eshirvana Jan 25 '21 at 14:34
  • 3
    Please stop using antiquated comma separated join style. Explicit `JOIN` syntax is much easier to read and write. – HoneyBadger Jan 25 '21 at 14:37
  • Do you remove from the list actors that are also SN=1, I guess not. Also maybe try with a recursive CTE for clarity (see [here](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query)) – nfgl Jan 25 '21 at 14:39
  • OUTPUT : Actor_Name 0 Freida Pinto 1 Rohan Chand 2 Damian Young 3 Waris Ahluwalia 4 Caroline Christl Long 5 Rajeev Pahuja 6 Michelle Santiago 7 Alicia Vikander 8 Dominic West 9 Walton Goggins (25698, 1) – Mahesh Patil Jan 25 '21 at 14:43

0 Answers0