Query multiple fields in table

[mjpr54]

Table is Movies
Fields are Title,actor1, actor2 etc
would like to be able to query all actor fields to see what movies they are in. In other words I would like to see all movies a certain actor appears in. New to this please forgive my ignorance......any suggestions

 

[RainLover]

You need to reconstruct your Table.

One for Movies
Second for Actors
Third for Actors and Movies. (Know as Many to Many)

Please read up on normalisation to gain the basics of Database Design.

 

[John Big Booty]

Welcome to the forum.

Instead of having a number of actor fields in your Movie Table, you would be better off have a separate actor table and then linking the tow via junction table. This structure would be more normalised, and also make your current task easier.

For an example of how this might look have a look at the sample posted in Post #11 of this thread.

 

[John Big Booty]

@ RainLover; Great mind's and all that

 

[mjpr54]

Thank you both. I will redesign and see what happens.

 

[mjpr54]

Ok I believe I have corrected as youu suggested and looked at your examples. The problem I have is that a movie will generally have more than one actor....How do I handle that ??

 

[John Big Booty]

... and actors will generally appear in more than one movie So in addition to your films table you will need an Actors table. Now these two tables can't naturally be linked, so what you need is a Junction Table which allows you to link these two disparate tables together. In that way you can have a number of different actors linked to each film and a number of different films linked to each actor.

 

[mjpr54]

Got that part.....sorry for being such a dense beginner....i guess what i am asking is how do i enter the data.....I buy a new movie and want to enter it in db along with director name and names of actors and genre etc. I already have a table populated with actors and would like to be able to select from that table or add to it.....but I ramble.... what am i missing or doing wrong ? Thanks in advance.

 

[John Big Booty]

The attached shows one way this might be done.

 

[mjpr54]

Thanks, I will look at this and see if I can adapt for my use