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

Question

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;

Answer 1

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;