in reply to Re: Normalisation Question (OT but trust me, perl implementation)
in thread Normalisation Question (OT but trust me, perl implementation)
mysql> select * from people; +----+-------+-------+-------+-------+ | id | first | last | birth | death | +----+-------+-------+-------+-------+ | 1 | John | Smith | NULL | NULL | | 4 | Sally | Smith | NULL | NULL | +----+-------+-------+-------+-------+ mysql> select * from characters; +----+----------+---------+ | id | first | last | +----+----------+---------+ | 1 | Jake | Jones | | 2 | EvilJake | Jones | | 3 | Shelley | Sellers | +----+----------+---------+ mysql> select * from people_characters; +--------+------------+ | people | characters | +--------+------------+ | 1 | 1 | | 1 | 2 | | 4 | 3 | +--------+------------+So, if we want SQL to fetch all characters played by John Smith, we need to join characters to people via the people_characters table:
Now, if these tables were set up properly with Class::DBI, fetching the characters of a given person should be as easy as:my $sth = $dbh->prepare(' SELECT characters.first, characters.last FROM characters INNER JOIN people_characters ON characters.id = people_characters.characters INNER JOIN people ON people_characters.people = people.id WHERE people.first = ? AND people.last = ? ORDER by characters.last, characters.first '); $sth->execute(qw(John Smith)); while (my $row = $sth->fetchrow_hashref) { print "$row->{first} $row->{last}\n"; }
I know that's a lot to digest (and wait until you need to add and delete people and/or characters!!), but hopefully this will be useful to you when you need it. Good luck. :)use MyDBI::people; my ($person) = MyDBI::people->search(first=>'John', last=>'Smith'); my @characters = $person->characters;
jeffa
L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re: 2Re: Normalisation Question (OT but trust me, perl implementation)
by halley (Prior) on Aug 04, 2003 at 17:40 UTC | |
by Cody Pendant (Prior) on Aug 04, 2003 at 22:58 UTC |