OK, I'm going to assume a database design like the following. It's rather simplified, but it'll get the idea across.

Actors
------
A_ID           integer
A_Fullname     varchar

Episodes
--------
E_ID           integer
E_Title        varchar

Roles
-----
R_ID           integer
R_Name         varchar

Actor_Role_Ep
-------------
ARE_ID         integer
ARE_Actor_ID   FK References Actor(A_ID)
ARE_Episode_ID FK References Episode(E_ID)
ARE_Role_ID    FK References Role(R_ID)

Now, if you have some actors in multiple roles, or some roles played by multiple actors, your SQL statement will return multiple rows. So you should then loop through the results in a while loop. Example code below.

# I'm assuming a valid $dbh exists.

my $sql = $dbh->prepare_cached("
SELECT ARE_ID, A_Fullname, E_Title, R_Name
FROM   Actor_Role_Ep, Actor, Episode, Role
WHERE  ARE_Actor_ID   = A_ID
AND    ARE_Role_ID    = R_ID
AND    ARE_Episode_ID = E_ID
WHERE  (whatever you want here);");

$sql->execute();
# If you need to pass in information above, then put the variables inside the ()'s.

while (($are_id, $a_name, $e_title, $r_name) = $sql->fetchrow_array) {
  print "$a_name played $r_name in episode $e_title.\n";
}

I hope that helps you..

Yes, sorry I didn't say "I know this isn't strictly Perl" -- I usually do.

To set the record straight, I am doing it in Perl, and for more detail of what I'm doing with DBI -- what the tables look like is more or less irrelevant, but essentially my select is:

select actor_name, episode_title, character_name
FROM <my tables>
WHERE <certain table columns match> AND actor_id = $id
[download]

And this results in rows representing a list of episodes an actor has appeared in. Most of the time he or she played the same character, so the character_name is the same in every row.

I wanted to be able to do a preliminary type of select which returned either one or more-than-one rows depending on whether that actor played one or more roles in total.

It does seem that GROUP BY will do what I want. Thanks.

---




($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss')
 =~y~b-v~a-z~s;                             print
[download]

Slightly off topic... but since you mentioned it: PLEASE don't actually put literal values into your sql by string interpolation: $sql = "... thing_id = $thing_id ...";. Do it with placeholders and bind values. This can be more and less of a performance issue with different types of databases, but it is always a security issue. In some databases, I could specify a $thing_id of "5; drop table thing", and you'd be hating life. On databases where that sort of thing can't be made to work (like oracle, for example), I could still plant a denial of service attack by saying that $thing_id was

"(select min(thing1.thing_id)
from
    thing thing1,
    thing thing2,
    thing thing3,
    thing thing4,
    thing thing5,
    thing thing6,
    thing thing7,
    thing thing8,
    thing thing9,
    thing thing10
--look!  no 'where', clause this is a 10-way cartesian product of thin
+g!
)"
[download]

You can come back in a month when your database finishes processing that query.

------------
:Wq
Not an editor command: Wq

I just wish I could lay my hands on the book where this was given as an example! Maybe if Cody read that he would find the answer??