Topic awaiting preservation: help with SQL query please

I have a database like this :

+----------------+
| Movies             |
+----------------+
| movie              |
| movietype       |
| people             |
+----------------+

The people table has :

+--------------------+
| People                   |
+--------------------+
| people_id              |
| people_fullname   |
| peple_isactor        |
| people_isdirector  |
+--------------------+

The people_isactor and people_isdirector are going to be either 1 or 0 (yes or no). When I run this :

"SELECT movie.movie_name, movie.movie_year, movietype.movietype_label, people.people_fullname " .
		"FROM movie, movietype, people " .
		"WHERE movie.movie_type = movietype_id AND people.people_id = movie.movie_leadactor " .
		"ORDER BY movie.movie_type";

It does just fine to display the movie name, movie year, movie type and lead actors name. I need the director too but can't figure out how to put that in there as well.

Do I need to set up the database different or does anyone have a good suggestion on how to get both actor and director in one query?

Thanks in advance!

Later,

C:\

Add AND people.´people_id = movie.movie_director ???

tried that. comes back with 0 entires

Later,

C:\

Perhaps

"SELECT movie.movie_name, movie.movie_year, movietype.movietype_label, people1.people_fullname, people2.people_fullname " .
    "FROM movie, movietype, people AS people1, people AS people2 " .
   "WHERE movie.movie_type = movietype_id AND people1.people_id = movie.movie_leadactor and people2.people_id = movie.movie_director" .
"ORDER BY movie.movie_type";

?

i thought you had it there kuckus. the people2.people_id = movie.movie_director returned 0 results.

do i need to put more of the database here? There is a table called "movie" that has a column "movie_actor" and "movie_director". These fields are populated with the "people_id" from the "people" table. People_id is auto incremented.

Later,

C:\

Hey CP,

Try this. I tried to reproduce your setup locally (with tables and such), but couldn't get a similar query going. Give this a shot; likely won't work out of the box, but might put you on the right track.

"SELECT movie.movie_name, movie.movie_year, movietype.movietype_label, directors.people_fullname, actors.people_fullname " .
		"FROM movie, movietype" .
		"LEFT JOIN people AS actors ON people.people_id = movie.movie_leadactor" .
		"LEFT JOIN people AS directors ON people.people_id = movie.movie_director" .
		"WHERE movie.movie_type = movietype.movietype_id" .
		"ORDER BY movie.movie_type";

Cheers,
DC

Edit: added the string concatenators I forgot.

(Edited by divinechaos on 09-10-2006 07:14)

Sorry to tell you, but your database setup flawed.
It's getting better with the details you told us later on, but still, there is no such thing as a 2:n relationship.
There are n:n relations ships, which in my mind, movieseople would be. So you'd make another table,
moveid | peopleId ( personId? ) which would contain the link, and join on that.
Then the rest is simple .

Select movie.movie_name, people.people_fullname, people_isActor, people.is_Director ( personally, I hate that prefixing. We're alreading accessing a table called people...)
 from movies, people, movieToPeople where movies.movieId = movieToPeople.movieId AND movieToPeople.peopleId = people.peopleId

and you get a list of all people, even if you take to store lead head stylist as well.

Ok think i made a mistake with my anser try OR instead of AND

TP, I think you are right. I'll give the extra table a shot and see what I can come up with. Thanks!

Later,

C:\