Query multiple fields in table

mjpr54

Registered User.
Local time
Today, 11:26
Joined
Feb 21, 2012
Messages
19
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
 
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.
 
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.
 
Thank you both. I will redesign and see what happens.
 
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 ??
 
... and actors will generally appear in more than one movie :D 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.
 
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.
 
Thanks, I will look at this and see if I can adapt for my use
 

Users who are viewing this thread

Back
Top Bottom