Recursive CTE using inner join in SQL

Question

I have to make an Recursive CTE to give me back all the actors who knew kevin bacon, to get better idea about my database I have Movies table and Actors and Movies2Actors table that contains only the actorid and the movieid, I need to get only the one who knew bacon through only two actors For example, you might wonder how Alfred Hitchcock can be connected to Kevin Bacon. One answer is that: Alfred Hitchcock was in Show Business at War (1943) with Orson Welles, and Orson Welles was in A Safe Place (1971) with Jack Nicholson, and Jack Nicholson was in A Few Good Men (1992) with Kevin Bacon! i am trying two different ways one is giving me an empty set, and the other is giving me this error message 'The full select of the recursive general table expression DBMASTER. BACON" must be the UNION link of two or more fullselects and must not include a column function, clause GROUP BY, HAVING or ORDER BY still contain an explicit join with a CLAUSE ON.'

WITH bacon (actorid, bacon_number) AS (
SELECT UNIQUE actorid, 0 FROM movies2actors 
        WHERE actorid =  (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') UNION ALL
SELECT movies2actors.actorid, bacon.bacon_number + 1
FROM movies2actors, bacon 
       WHERE movies2actors.actorid IN 
            (SELECT UNIQUE actorid FROM movies2actors WHERE movieid IN (SELECT UNIQUE movieid FROM movies2actors 
                WHERE actorid = (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') )) 
            AND movies2actors.actorid <> (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') AND bacon.bacon_number<2  
)
SELECT bacon.actorid , bacon.bacon_number  FROM bacon ;

WITH bacon (actorid,relationid, bacon_number) AS (
SELECT UNIQUE actorid, actorid ,0 FROM ACTORS 
        WHERE name = 'Bacon, Kevin (I)'
UNION ALL
SELECT ACTORS.actorid,bacon.relationid, bacon.bacon_number + 1
FROM ACTORS
       JOIN  bacon ON ACTORS.actorid = bacon.relationid
       WHERE ACTORS.actorid IN 
            (SELECT UNIQUE actorid FROM movies2actors WHERE movieid IN (SELECT UNIQUE movieid FROM movies2actors 
                WHERE actorid = (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') )) 
            AND ACTORS.actorid <> (SELECT actorid FROM actors WHERE name = 'Bacon, Kevin (I)') AND bacon.bacon_number<2     
)
SELECT bacon.actorid , bacon.bacon_number  FROM bacon ;

score 0 · Answer 1 · answered Dec 18 '19 at 19:04

0

Use the old join syntax in the recusive CTE (in Db2 LUW)

table a,
table b
Where a.col = b.col

answered Dec 18 '19 at 19:04

MichaelTiefenbacher

3,805
2
11
17

score 0 · Answer 2 · answered Dec 18 '19 at 20:33

Try this:

/*
WITH 
  Movies (movieid, moviename) AS 
(
 VALUES
   (1, 'Show Business at War (1943)')
 , (2, 'A Safe Place (1971)')
 , (3, 'A Few Good Men (1992)')
)
, Actors (actorid, actorname) AS 
 (
 VALUES
   (1, 'Alfred Hitchcock')
 , (2, 'Orson Welles')
 , (3, 'Jack Nicholson')
 , (4, 'Kevin Bacon')
 )
 , Movies2Actors (movieid, actorid) AS
 (
 VALUES
  (1, 1)
, (1, 2)   
, (2, 2)   
, (2, 3)   
, (3, 3)
, (3, 4)
 )
, 
*/
bacon (actorid, level, chain) AS
(
SELECT mo.actorid, 1, cast('|'||trim(a.actorid)||'|'||trim(mo.actorid)||'|' AS varchar(1000))
FROM Actors a, Movies2Actors mb, Movies2Actors mo
WHERE a.actorname=
'Kevin Bacon' 
--'Jack Nicholson'
--'Orson Welles'
AND a.actorid=mb.actorid AND mb.movieid=mo.movieid
AND a.actorid<>mo.actorid 
  UNION ALL
SELECT mo.actorid, b.level+1, b.chain||trim(mo.actorid)||'|'
FROM bacon b, Movies2Actors mb, Movies2Actors mo
WHERE b.actorid=mb.actorid AND mb.movieid=mo.movieid
AND locate('|'||trim(mo.actorid)||'|', b.chain)=0 
)
SELECT DISTINCT a.actorname
--, b.*
FROM bacon b
JOIN Actors a ON a.actorid=b.actorid;

You may uncomment the commented out block with sample data and run the statement as is to check the result.
The chain column is to prevent recursion.

Recursive CTE using inner join in SQL

2 Answers2