Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Help me with a query

Author  Topic  Roxxor Starting Member 7 Posts Posted - 2008-09-15 : 17:33:32 I would like to select all members (show name of person and name of city) that have watched all movies with a specified actor and without using COUNT (last time I used COUNT I didn´t get it work). Here is a screenshot of the table structure: http://img369.imageshack.us/img369/3356/dbms4.jpg (note: the table which name is "MemberTi..." should be "MemberTicket". I have tried with this one but it just gives me the members that have watched one of the specified actor´s movies, not all of them: Code Snippet SELECT Person.name, City.name FROM Person, Member, City WHERE Person.pid IN ( SELECT pid FROM Member WHERE pid IN ( SELECT member_id FROM MemberTicket WHERE ticket_id IN ( SELECT ticket_id FROM Ticket WHERE sid IN( SELECT sid FROM Show WHERE movie_id IN( SELECT movie_id FROM Movie WHERE movie_id IN( SELECT movie_id FROM Cast WHERE artist_id IN { SELECT Artist.artist_id FROM Artist, Cast WHERE name = 'Brad Pitt' AND Cast.role_id = 1 ))))))) AND Person.pid = Member.pid AND Member.city_id= City.city_id The problem is that the SQL statement just selects those who have watched one of two or more movies with a specified actor. I would like to select all members that have watched all movies with a specified actor. Please, help!