sqlmysqlselect

SQL How to select the first actor of the current movie id and or show id who has > 0 films or shows


Sort of new to PHP (8.1) & SQL (Myqli) and seriously stuck on this one. Would very much appreciate some help here.

SQL Tables are as follows:

$currenttvshowid='27641';

tvshow: tvshowid title

tvshow_actors: actorid actorname

tvshow_starredin: actorid tvshowid role

where, for instance: $currentseriesid='27641'

tv_shows table

tvshowid title
27641 The Addams Family
18644 The Beverly Hillbillies
38235 Chico and the Man
26244 I Dream of Jeannie
... ...

tv_show_actors table

actorid actorname
47863 Carolyn Jones
20602 John Astin
40376 Jackie Coogan
20317 Ted Cassidy
18927 Buddy Ebsen
263177 Freddie Prinze
36613 Barbara Eden
... ...

tv_show_starredin table

actorid tvshowid role
47863 27641 Morticia Addams
20602 27641 Gomez Addams
40376 27641 Uncle Fester
20317 27641 Lurch
150863 27641 Grandmama
150864 27641 Wednesday Addams
150865 27641 Pugsley Addams
14067 27641 Cousin Itt
18927 18644 Jed "JD" Clampett
20317 18644
263177 38235 Chico Rodriguez
20317 38235
36613 26244 Jeannie
20317 26244 Habib
... ... ...

I would like to get the result of the other shows that actor Ted Cassidy (20317) played in based on the amount of times he's listed in the tv_show_starredin table excluding the current tv show.

tvshowid title
18644 The Beverly Hillbillies
38235 Chico and the Man
26244 I Dream of Jeannie

I'm trying to get the actorid and name of the first actor from the current tvshowid who has greater than 0 shows.

Basically want to count or list how many other TV shows actor Ted Cassidy actorid (20317) played in excluding the current tvshowid (27641) if that makes any sense?

SELECT a.*, t.*
    FROM tv_show_actors a
    JOIN tv_show_starredin t ON a.actor_id = t.actor_id
    WHERE t.tvshow_id IN (
        SELECT tvshow_id
        FROM tv_shows
        WHERE tvshow_id = '$currentseriesid'
    ) LIMIT 1;

Solution

  • Want to count or list how many other TV shows actor Ted Cassidy (20317) played in excluding the current tvshow id (27641)

    So, you basically want to count all tv_show_starredin entries for the actor except for the current TV show.

    set @actor_id = 20317;
    set @current_tvshow_id = 27641;
    
    select count(*)
    from tv_show_starredin
    where actor_id = @actor_id
    and tvshow_id <> @current_tvshow_id;
    

    This counts the roles the actor played. If you want to count a TV show only once where the actor played more than one role, use COUNT(DISTINCT tvshow_id):

    select count(distinct tvshow_id)
    from tv_show_starredin
    where actor_id = @actor_id
    and tvshow_id <> @current_tvshow_id;