Closed Thread Icon

Topic awaiting preservation: help with SQL query please (Page 1 of 1) Pages that link to <a href="https://ozoneasylum.com/backlink?for=28408" title="Pages that link to Topic awaiting preservation: help with SQL query please (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: help with SQL query please <span class="small">(Page 1 of 1)</span>\

 
CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

posted posted 09-09-2006 23:04

I have a database like this :

code:
+----------------+
| Movies             |
+----------------+
| movie              |
| movietype       |
| people             |
+----------------+




The people table has :


code:
+--------------------+
| 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 :

code:
"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:\

Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 09-09-2006 23:47

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

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

posted posted 09-09-2006 23:55

tried that. comes back with 0 entires

Later,

C:\

kuckus
Paranoid (IV) Mad Librarian

From: Glieberlermany
Insane since: Dec 2001

posted posted 09-10-2006 00:19

Perhaps

code:
"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";



?

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

posted posted 09-10-2006 02:20

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:\

divinechaos
Bipolar (III) Inmate

From:
Insane since: Dec 2001

posted posted 09-10-2006 07:13

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.

code:
"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)

Tyberius Prime
Maniac (V) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 09-10-2006 09:15

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 .

code:
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.

Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 09-10-2006 11:26

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

CPrompt
Maniac (V) Inmate

From: there...no..there.....
Insane since: May 2001

posted posted 09-10-2006 15:51

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:\

« BackwardsOnwards »

Show Forum Drop Down Menu