Query For Multiple Criteria Related to 1 Or More Unique Elements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I asked this question a bit earlier, but feel that I might need to be more
clear on my need.

Here is an example of my goal. Lets say we have a database of films. Each
film has a related table of cast members. What we want to do is be able to
supply 2 or more members of a cast to see other films those members have
worked on together.

If anyone can help with this type of query, your assistance will be very
much appreciated.

Thanks again in advance!
 
Assuming a table of candidates with id_crew that you want to check, and a
Cast table with id_Film and id_crew in it, this query should find all films
that more than one of the candidates worked on:-

Q_MoreThanOneCandidate

SELECT [Cast].[id_Film], Sum(1) AS ct
FROM [Cast] INNER JOIN candidates ON [Cast].[id_crew] =
[candidates].[id_crew]
GROUP BY [Cast].[id_Film]
HAVING Sum(1)>1;

This query should list all films with which any number of candidates worked
on.
Q_FilmsWithCandidates:-

SELECT [Cast].[id_Film], [Candidates].[id_crew]
FROM [Cast] INNER JOIN [Candidates] ON [Cast].[id_crew] =
[candidates].[id_crew];

and this one should list all of the films on which more than one candidate
worked, listing the candidates.

SELECT Q_FilmsWithCandidates.[id_Film], Q_FilmsWithCandidates.[id_crew]
FROM Q_FilmsWithCandidates INNER JOIN Q_MoreThanOneCandidate ON
Q_FilmsWithCandidates.[id_Film] = Q_MoreThanOneCandidate.[id_Film];

These queries were constructed from tested queries changing the names to
match the situation. Errors may have crept in.

David F. Cox, Bedford, UK
(Someone is filming using the historic Cardington Airship sheds near here.
You never know your luck ...)
 
Back
Top