Query Challenge (Is this query possible?) 1

[whoschad]

I can't seem to wrap my mind around this query. I'm not sure if it's even possible.

I have a personal Movie Database and I'm trying to write an SQL Query which will do the following:

When given two different movie titles, I would like the query to find a third movie title (if it exists) that contains an actor from each of the first two.

For example, if I were to enter the movies "Seven" and "American History X", I would like to have the query return "Fight Club" (because Brad Pitt from 'Seven' and Edward Norton from 'American History X' are each in it), also, it would return "Runaway Jury" because Leland Orser from 'Seven' and Guy Torry from 'American History X' were both in that one.


Here is the relevant table information:

TABLE: Movies
FIELDS: MovieID, Title

TABLE: StarredIn
FIELDS: MovieID, ActorID

TABLE: Actors
FIELDS: ActorID, ActorName


Please let me know if this is possible and if so, how to accomplish it. I'd like to handle it in one SQL statement rather than code.

Thanks.

 

[r937]

try this --

select M2.Title
  from ((
       Movies as M1
inner
  join StarredIn as S1
    on S1.MovieID = M1.MovieID
       )
inner
  join StarredIn as S2
    on S2.ActorID = S1.ActorID
   and S2.MovieID <> S1.MovieID
       )
inner
  join Movies as M2
    on M2.ActorID = S2.ActorID
 where M1.title in
       ('Seven','American History X')  
group
    by M2.Title
having count(*) > 1

r937.com | rudy.ca

 

[whoschad]

Absolutely perfect.

I just had to change 'on M2.ActorID = S2.ActorID' to 'on M2.MovieID = S2.MovieID'.

Probably just a typo.


Thanks a million.

 

[r937]

yep, a copy/paste typo



r937.com | rudy.ca

 

[whoschad]

Actually, you know what? This query will sometimes return too many rows.

If one of the movies in the search criteria contains two actors that are also in a third together, it will also return that movie.

For example, I ran 'Seven' and 'American History X' and got back 'Outbreak' because Morgan Freeman and Kevin Spacey were both in 'Outbreak' and 'Seven', but no actors from 'Outbreak' were in 'American History X'.

Any suggestions?

 

[r937]

in any other database system, i would suggest

HAVING COUNT(DISTINCT S2.ActorID) > 1

but since Access doesn't do COUNT DISTINCT, i'm kinda stuck at the moment

if you could email me a small MDB file, in Access 97 format, i might be able to work on it and come up with something

r937.com | rudy.ca

 

[cfStarlight]

Without COUNT(DISTINCT ..) the best I could come up with is something like this. (Using r937's code

SELECT	DISTINCT m.title
FROM  (((
		   movies m 
	INNER 
		JOIN starredin s1 
		ON m.movieid = s1.movieid )
	INNER 
		JOIN starredin ms1 
		ON	s1.actorid = ms1.actorid )
	INNER 
		JOIN movies m1 
		ON	(m1.movieid = ms1.movieid 
		AND m1.Title IN ('American History X', 'Seven'))
		)
WHERE	m.title NOT IN ('American History X', 'Seven')		
GROUP BY 
		m.title
HAVING  
		MAX(IIF(m1.title = 'American History X', 1, 0)) = 1 AND
        MAX(IIF(m1.title = 'Seven', 1, 0)) = 1