Hello, I'm using Access and need to code this query on SQL and I have this problem:

I have 3 tables: Movie, Actor, Acts_In

Movie table has fields: Movie_ID, movie_name, actor_1, actor_2, director

Actor table has fields: Actor_ID, first_name, last_name

Acts_IN table has fields: Movie_ID, Actor_ID

* actor_id and movie_id are primary keys

I have to create a query that allows me to search for a director, giving me a list of actors that have worked with that director, and all the movies they have acted in.

so far I have come up with this code, however:

select actor.first_name, actor.last_name, movie_name

from (actor inner join acts_in ON acts_in.actor_ID=actor.actor_id) inner JOIN movie ON acts_in.movie_ID=movie.movie_id

group by actor.first_name, actor.last_name, movie_name;

This code gives me a list of all actors and all movies they acted in, but it does not give me specific actors in respect to a specific director, adding

where director="XYZ"

does not work as it gives me all actors that have worked with director XYZ but not all movies they have acted in. 

Thanks!

E.g.

SELECT  *
FROM    (movie M
        INNER JOIN acts_in AI ON AI.movie_ID = M.movie_id)
        INNER JOIN actor A ON A.actor_ID = AI.actor_id
WHERE   M.director = '';

There's no need for a GROUP BY.

Unfortunately, that did not work, as it produces the same results as my first code, which only brings the movies the actor acted in with the specific director, not ALL the movies the actor acted in regardless of director.

Okay, haven't read this part. E.g.

SELECT  *
FROM	movie M
	INNER JOIN acts_in AI ON AI.movie_ID = M.movie_id
WHERE	AI.actor_ID IN (
	SELECT  A.actor_ID
	FROM    (movie M
			INNER JOIN acts_in AI ON AI.movie_ID = M.movie_id)
			INNER JOIN actor A ON A.actor_ID = AI.actor_id
	WHERE   M.director = ''
);

Please provide a little more detail, ideally sample data in the form of a query that creates sample data as a temp table, as well as your desired results.

Also... when you say this is an Access database, did you mean it's an Access database connected to SQL Server, or just that it's an access database?  Because there are also some syntax differences once queries start to get complicated in Access vs. SQL Server. 

This is untested, but is probably getting close to what you're looking for.

SELECT  *
FROM    (movie M
        INNER JOIN acts_in AI ON AI.movie_ID = M.movie_id)
        INNER JOIN actor A ON A.actor_ID = AI.actor_id
        LEFT JOIN Acts_IN AI2 on AI2.Actor_ID = A.Actor
        LEFT JOIN Movie M2 on M2.Movie_ID AI2.Movie_ID
WHERE   M.director = '';

Whenever I insert a name into the last line: where director = "XYZ", it gives me a syntax error. ideally this is what i want it to look like

http://s7.postimg.org/o3uz63j8r/feef.png

the top query is wrong where it only shows the movies directed by tim burton, however the bottom one is the one I want where is shows ALL movies acted by the actors, from ALL directors.

 

this is just basic Microsoft access. about your code, it gives me a syntax error, i honestly don't know what is needs to be fixed, but thank you for your effort. ideally this is what i want it to look like.

http://s7.postimg.org/o3uz63j8r/feef.png

the top query is wrong where it only shows the movies directed by tim burton, however the bottom one is the one I want where is shows ALL movies acted by the actors, from ALL directors.

Ok, one last quick attempt, but keep in mind this is the SQL Server forum. Assuming this works from your original post, except for exploding the movies actors have been in:

select actor.first_name, actor.last_name, movie_name
from (actor inner join acts_in ON acts_in.actor_ID=actor.actor_id) inner JOIN movie ON acts_in.movie_ID=movie.movie_id
group by actor.first_name, actor.last_name, movie_name
where director="XYZ"

Then keep that in the middle, but add this around it (I'm pretending there is something like an ActorID or other key for each actor):

Select * from Actors where ActorID in
(
select actor.actorID
from (actor inner join acts_in ON acts_in.actor_ID=actor.actor_id) inner JOIN movie ON acts_in.movie_ID=movie.movie_id
group by actor.first_name, actor.last_name, movie_name
where director="XYZ"
) as DERIVED_QUERY_NAME

I don't remember whether Access does or doesn't want the word "as" on the last line.  Given what you've done so far, this should get you in the right direction.

EDIT: Subquery needed to only return Actor_ID (or something equivalent), had to take out other columns

• Edited by johnqflorida 10 hours 32 minutes ago Added Edit

Grump. You should have provided us example data and DML, like this:

DECLARE @movies TABLE (movie_ID INT, movie_Name VARCHAR(50), director VARCHAR(75))
DECLARE @actors TABLE (actor_ID INT, first_name VARCHAR(50), last_name VARCHAR(50))
DECLARE @acts_in TABLE (movie_ID INT, actor_ID INT)

INSERT INTO @movies (movie_ID, movie_Name, director) VALUES
(1, 'Batman Returns', 'Tim Burton'),
(2, 'Charlie and the Chocolate Factory', 'Tim Burton'),
(3, 'Sweeney Todd', 'Tim Burton'),
(4, 'Alice in Wonderland', 'Tim Burton'),
(5, 'Edward Scissor Hands', 'Tim Burton'),
(6, 'From Hell', 'Albert Huges')

INSERT INTO @actors (actor_ID, first_name, last_name) VALUES
(1, 'Danny', 'DeVito'),
(2, 'Freddie', 'Highmore'),
(3, 'Helena', 'Bonham-Carter'),
(4, 'Johnny', 'Depp'),
(5, 'Mia', 'Wasikowska'),
(6, 'Micheal', 'Keaton'),
(7, 'Winona', 'Ryder')

INSERT INTO @acts_in (movie_id, actor_ID) VALUES
(1, 1),(1, 6),
(2, 2),(2, 4),
(3, 3),(3, 4),
(4, 4),(4, 5),
(5, 4),(5, 7),
(6, 4)

Which would have allowed us to come up with this:

SELECT m2.director, a.first_name, a.last_name, m2.movie_Name
  FROM ((((@movies m
    INNER JOIN @acts_in ai
	  ON m.movie_ID = ai.movie_ID)
	INNER JOIN @actors a
	  ON ai.actor_ID = a.actor_ID)
	INNER JOIN @acts_in ai2
	  ON a.actor_ID = ai2.actor_ID)
	INNER JOIN @movies m2
	  ON ai2.movie_ID = m2.movie_ID)
 WHERE m.director = 'Tim Burton'
 GROUP BY m2.director, a.first_name, a.last_name, m2.movie_Name

IIRC the primary difference between TSQL and the "Access SQL" is the freakin parens. So that *should* run in access, which you appear to be using.

I LOVE YOU !

THANK YOU SO MUCH !