I've got a nice database set up for a TV show.

I've got episodes in one table, actors in another, and roles in a third, and I've got a table of actor_role_ep mappings, and that all works well and is normalised as far as I can make it.

My problem comes in special cases: there's one role which was played by two different actors, and there's a couple of actors who have played two different roles.

If there was always a one-to-one mapping, I'd just go "the character Joe Smith, played by Jack Jones, appears in the following eps:" then list them.

But in the special cases, I don't know how to test for "all appearances played by same guy" or "all appearances in show are as same character" except by going through the whole hashref and saying "if actor ne previous actor" kind of thing -- testing each value to see they're all the same. Or, of course, hand-coding the exceptions in like "if id=473, do something different" which would be kind of lame.

What can I do to select the appearances in a smart SQL way and get an immediate answer to "does more than one actor play this role?"? 


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

[download]

In reply to SQL query: are all results in one hashref key the same? by Cody Pendant

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.