Programming Help

Web Development



What's Here?

  • Members: 132,347
  • Replies: 471,010
  • Topics: 73,016
  • Snippets: 2,540
  • Tutorials: 666
  • Total Online: 1,158
  • Members: 64
  • Guests: 1,094

Who's Online?

Welcome to Dream.In.Code
Getting Help is Easy!

Join 132,347 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,158 people online right now. Registration is fast and FREE... Join Now!




SQL Query

 
 Reply to this topicStart new topic

SQL Query
hallizh
post 22 Sep, 2008 - 06:32 PM
Post #1


New D.I.C Head

*
Joined: 9 Mar, 2008
Posts: 37


My Contributions
SQL

SELECT M.title
FROM movie M, actor A
WHERE M.director = A.id

These are 2 tables, one for actors and one for movies, theres also 3rd one for casting.

Im supposed to get an output showing every film where the director is also on actor.

What am i doing wrong?

Thanks ! smile.gif

EDIT: Oh yeah, this query just outputs every film in the database.

This post has been edited by hallizh: 22 Sep, 2008 - 06:34 PM
User is offlineProfile CardPM

Go to the top of the page

kyrotomia
post 22 Sep, 2008 - 08:20 PM
Post #2


New D.I.C Head

*
Joined: 5 May, 2007
Posts: 45



Thanked 1 times
My Contributions
You need also to specify that the movies has to be equal. I'm not quite sure how your tables are builded, but you would beed something similar too :
CODE


SELECT M.title
FROM movie M, actor A
WHERE M.director = A.id
AND M.id = A.filmID;


If your actors/film relation is in your casting, which I suppose, you would need to use that casting table in that statement too. Have a look at that. Good luck!.

Edit : little error in code.

This post has been edited by kyrotomia: 22 Sep, 2008 - 08:20 PM
User is offlineProfile CardPM

Go to the top of the page

Trogdor
post 23 Sep, 2008 - 01:29 AM
Post #3


D.I.C Addict

Group Icon
Joined: 6 Oct, 2006
Posts: 517



Thanked 3 times

Dream Kudos: 125
My Contributions
Does it need to be that the director is acting in the same film, or that he is acting in _any_ film?
In that case you will need to join twice to the film table under different aliasses.
User is offlineProfile CardPM

Go to the top of the page

baavgai
post 23 Sep, 2008 - 04:22 AM
Post #4


Dreaming Coder

Group Icon
Joined: 16 Oct, 2007
Posts: 1,962



Thanked 95 times

Dream Kudos: 475

Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua

My Contributions
I'm afraid we don't have enough info. What are the fields the three tables? Can their be only one director for a movie? Is cast a list of movie-actor pairs? Let's a assume your schema looks something like this:
CODE

Movie: MovieId, Name, DirectorId, ...
Cast: MovieId, ActorId


Them a valid answer might be:
CODE

select distinct m.*
   from Movie m
      inner join Cast c
         on m.MovieId=c.MovieId
            and m.DirectorId=c.ActorId
User is offlineProfile CardPM

Go to the top of the page

Trogdor
post 23 Sep, 2008 - 08:07 AM
Post #5


D.I.C Addict

Group Icon
Joined: 6 Oct, 2006
Posts: 517



Thanked 3 times

Dream Kudos: 125
My Contributions
That is correct in the case the director should be acting in the movie he directs.
I also assumed that was what he meant, but from the original question you can not be 100% sure...
User is offlineProfile CardPM

Go to the top of the page

hallizh
post 9 Oct, 2008 - 03:11 AM
Post #6


New D.I.C Head

*
Joined: 9 Mar, 2008
Posts: 37


My Contributions
Thanks guys, sorry for the lack of information.
User is offlineProfile CardPM

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 11/22/08 03:49AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month