SQL question

software development

#1

Well I only know basic SQL commands and creating a database.

But I’m wondering how would it be best to implemet a field to carry multiple entries pointing to another table.

An example is like the Actors field of a Movie database.

Each movie carries various different actors. If I want more info of the individual actor, I would pull the info from the Actor database.

Is it possible to put like multiple foreign keys into one field?

Any help would be appreciative.


#2

Yes. It’s called a many to many relationship.
The most common way to solve it is to just create a movie_actor table with two columns, and index them both. There should be a multiple/foreign key thing in phpmyadmin…Yes, just make them both primary keys when creating the table.
As for actually coding it, I’m not too sure 8-). I’ve never known what happens when you want a single record and crossing that with something that would return multiple values. ie one movie with many actors.


#3

You do not want the join table (movie_actor) to have primary keys, because neither column will be unique. The SQL for retrieving information about a specific actor would be like this:

SELECT * FROM actor WHERE name='Brad Pitt';To get all of Brad’s movies in the database (and assuming that his actor ID is “1”), you would need a join, like this:

SELECT * FROM movie, movie_actor WHERE movie.id = move_actor.movie_id AND movie_actor.actor_id = 1;The WHERE clause performs the join (otherwise you get a cartesian product), and the AND clause limits the results to just those movies that starred actor number 1 (Brad Pitt).


Simon Jessey
Keystone Websites | si-blog