Re: Difficult query ?

It seems to be a normalization problem. I tought the Movie table has only one Actor per record . If you normalize your database I think the solution will come up . You'll need three tables at least. The Actor table, the Movie table and the Cast table wich is an association between Movie and Actor. Actor ==>one to many==>Cast, Movie ==>one to many==>Cast. This way you can get from Cast all the Movie that Actor made and all the Actor that worked in Movie. Jayme. http://www.conex.com.br/jjeffman -----Mensagem Original----- De: Troy Settle <troy@psknet.com> Para: Jeroen ten Berge <j.ten.berge@getexo.nl>; PHP Database <php-db@lists.php.net> Enviada em: segunda-feira, 16 de outubro de 2000 10:14 Assunto: RE: [PHP-DB] Difficult query ? > > What happens if you have 20 actors in a given movie? Even then, it's > not accurate. There could be any number of actors in a given movie, 1 > to infinity. > > Anyways, with your given schema, here's a query that should work for > you: > > SELECT title > FROM table > WHERE actor1='$chosen_actor' > OR actor2='$chosen_actor' > OR actor3='$chosen_actor'; > > It's ugly, and your life as a DB programmer is getting off to an overly > difficult start. > > -- > Troy Settle > Pulaski Networks > 540.994.4254 > > > ** -----Original Message----- > ** From: Jeroen ten Berge [mailto:j.ten.berge@getexo.nl] > ** Sent: Monday, October 16, 2000 6:08 AM > ** To: PHP Database > ** Subject: RE: [PHP-DB] Difficult query ? > ** > ** > ** You're right, but I have to stick with the existing setup :( > ** > ** Klote hé ? Dus heb ik t maar met een union query gedaan. > ** > ** > ** -----Original Message----- > ** From: Disser, Arno [mailto:Disser_A@sfs.nl] > ** Sent: Monday, October 16, 2000 11:23 AM > ** To: PHP Database > ** Cc: 'Jeroen ten Berge' > ** Subject: RE: [PHP-DB] Difficult query ? > ** > ** > ** Not to difficult, bu you need to reconsider your datamodel first. > ** > ** Define a table MOVIES, in wich you do insert your movie titles. > ** Define a table ACTORS, in wich you do insert your actors. > ** Define a table ACTSIN, in wich you insert the films and it's actors. > ** (example in pseudo code: > ** > ** Define tables: > ** ACTORS(id,name) > ** MOVIES(id,name) > ** ACTSIN(id.movies_id,actors_id) > ** > ** Required query: > ** select movie.name > ** from ACTORS, > ** MOVIES, > ** ACTSIN > ** where ACTSIN.movies_id = MOVIES.id and > ** ACTSIN.actors_id = ACTORS.id and > ** ACTORS.name = 'Any given variable'; > ** > ** As you see, you get more a flexible data structure. > ** (B.T.W. This method is called Normalisation, and works fine > ** on any kind of > ** database!) > ** > ** > ** ------------------------------------------------ > ** Arno Disser > ** SFS/Pensioenen en Verzekeringen > ** FAD/ICT > ** Postbus 11 > ** 2280 AA RIJSWIJK The Netherlands > ** Tel +31 (0)70 33 66 389 > ** Fax +31 (0)70 31 93 884 > ** email: <mailto:disser_a@sfs.nl> > ** ------------------------------------------------ > ** > ** -----Oorspronkelijk bericht----- > ** Van: Jeroen ten Berge [mailto:j.ten.berge@getexo.nl] > ** Verzonden: zaterdag 14 oktober 2000 19:33 > ** Aan: PHP Database > ** Onderwerp: [PHP-DB] Difficult query ? > ** > ** > ** Hi, I have 1 table with the rows Actor1,Actor2,Actor3 and title; > ** What I want is 1 select box where you can type the actor and > ** then get a list > ** of the movies this actor is in... > ** > ** Anybody ideas ? > ** > ** > ** -- > ** PHP Database Mailing List (http://www.php.net/) > ** To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > ** For additional commands, e-mail: php-db-help@lists.php.net > ** To contact the list administrators, e-mail: > ** php-list-admin@lists.php.net > ** > ** -- > ** PHP Database Mailing List (http://www.php.net/) > ** To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > ** For additional commands, e-mail: php-db-help@lists.php.net > ** To contact the list administrators, e-mail: > ** php-list-admin@lists.php.net > ** > ** > ** -- > ** PHP Database Mailing List (http://www.php.net/) > ** To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > ** For additional commands, e-mail: php-db-help@lists.php.net > ** To contact the list administrators, e-mail: > ** php-list-admin@lists.php.net > ** > ** > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net >