CODEWITHSUNDEEP
sqlsql-server
Learner
Learner

Reputation: 571

Find records with the same id and put in the same row

I have three simple tables Actor, Film, Film_actor.

Film_Actor table: 

 Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id    | smallint                    | not null
film_id     | smallint                    | not null

Actor table:

 Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id    | integer                     | not null 
first_name  | character varying(45)       | not null
last_name   | character varying(45)       | not null

Film_actor table:

 Column     | Type                        | Modifiers
------------+-----------------------------+----------
film_id     | integer                     | not null
title       | character varying(255)      | not null

My goal is to find two actors who cast together the most and list the titles of those movies. Order the result set alphabetically. In addition, actor_id of the first_actor should be lower than actor_id of the second_actor.

So the desired output should look like this:

first_actor     | second_actor  | title
----------------+---------------+---------------
Daniel Craig    | Eva Green     | Casino Royale

The best I can think of is just to create new derived table and try to get data, but this query gives so many recurring rows:

SELECT
     CASE
        WHEN ac.actor_id < te.actor_id THEN ac.first_name
        ELSE te.first_name
     END
   , CASE
        WHEN ac.actor_id > te.actor_id THEN te.first_name
        ELSE ac.first_name
     END
   , fi.title
   , fi.film_id
FROM Film_Actor as fa
INNER JOIN Actor as ac ON fa.actor_id = ac.actor_id
INNER JOIN Film as fi ON fa.film_id = fi.film_id
INNER JOIN ( SELECT a_c.first_name, a_c.last_name, a_c.actor_id  FROM 
Film_Actor as f_a 
    INNER JOIN Actor as a_c ON f_a.actor_id = a_c.actor_id) te
    ON te.actor_id=fa.actor_id

Guys, please help to make a query, as I have just basic knowledge of SQL. Thanks in advance!

It is not homework, it is simple example what I need to do. Real tables are not Films and Actors. My knowledge allows to join, but I do not know how I can put the same actors in one row. And now I limit to this knowledge, but I try to learn!

Upvotes: 3

Views: 374

Answers (2)

Nolan Shang
Nolan Shang

Reputation: 2328

I wish it is useful for you.

Combining all actor one by one, then get the file that they [lay together.

CREATE TABLE #Film_actor(actor_id INT,film_id INT )
CREATE TABLE #Actor(actor_id INT,first_name VARCHAR(100),last_name VARCHAR(100))
CREATE TABLE #film(film_id INT,title VARCHAR(255))
INSERT INTO #Actor(actor_id,first_name,last_name)
SELECT 1,'Daniel','Craig' UNION ALL
SELECT 2,'Eva','Green' UNION ALL
SELECT 3,'John','White' 
INSERT INTO #film(film_id,title)
SELECT 1,'Casino Royale' UNION ALL
SELECT 2,'Windows' UNION ALL
SELECT 3,'Film3' UNION ALL
SELECT 4,'Film4' UNION ALL
SELECT 5,'Film5' 
INSERT INTO #Film_actor(actor_id,film_id)
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,5

SELECT a1.first_name+' '+a1.last_name AS first_actor,
       a2.first_name+' '+a2.last_name AS second_actor,
       f.title
       ,COUNT(0)OVER(PARTITION BY a1.actor_id,a2.actor_id) TotalFileCount
FROM #Actor AS a1 INNER JOIN #Actor AS a2 ON a1.actor_id!=a2.actor_id
INNER JOIN #Film_actor AS fa1 ON fa1.actor_id=a1.actor_id
INNER JOIN #Film_actor AS fa2 ON fa2.film_id=fa1.film_id AND fa2.actor_id=a2.actor_id
LEFT JOIN #film AS f ON f.film_id=fa1.film_id

first_actor second_actor    title   TotalFileCount
Daniel Craig    Eva Green   Casino Royale   1
Daniel Craig    John White  Casino Royale   2
Daniel Craig    John White  Windows 2
Eva Green   Daniel Craig    Casino Royale   1
Eva Green   John White  Casino Royale   1
John White  Daniel Craig    Windows 2
John White  Daniel Craig    Casino Royale   2
John White  Eva Green   Casino Royale   1

Upvotes: 2

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this query.

;WITH TwoActors AS (
    SELECT   
        ac1.actor_id first_actor_id
        , ac1.first_name + ' ' + ac1.last_name   first_actor
        , ac2.actor_id second_actor_id
        , ac2.first_name + ' ' + ac2.last_name second_actor
        ,count(*) FilmCount
    FROM
        Actor as ac1 
        INNER JOIN Film_Actor as fa1 ON fa1.actor_id = ac1.actor_id
        INNER JOIN Film_Actor as fa2 ON fa1.film_id = fa2.film_id
        INNER JOIN Actor as ac2 ON fa2.actor_id = ac2.actor_id and fa1.actor_id < ac2.actor_id
        INNER JOIN Film as fi ON fa1.film_id = fi.film_id
    GROUP BY 
        ac1.actor_id
        , ac1.first_name
        , ac1.last_name   
        , ac2.actor_id
        , ac2.first_name
        , ac2.last_name

)
SELECT * FROM TwoActors
CROSS APPLY ( SELECT fi.title , fi.film_id 
                FROM  Film_Actor fa 
                INNER JOIN Film as fi ON fa.film_id = fi.film_id 
                WHERE fa.actor_id IN ( TwoActors.first_actor_id, TwoActors.second_actor_id )
                GROUP BY fi.title , fi.film_id
                HAVING COUNT(*) > 1 )AS F
ORDER BY FilmCount DESC

Upvotes: 2

Related Questions