Hi all,

I am trying to create SQL query with multiple joins (2 to start with) and I know from access that I have to creata first one join in one query and then use that query with another join. But how do I do this in SQL (I have to send SQL request to my Access DB from a webpage, so I have to write it dones as SQL. In access it gives me that result:

SELECT Query1.movie_title, Query1.movie_year, Query1.genre_id, tbl_users_comments.comment
FROM Query1 LEFT JOIN tbl_users_comments ON Query1.movie_id=tbl_users_comments.movie_id;

So I have already here Query1 which was created before. How can I do it all query1 and query2 (main big query) in one SQL query?

Thanks

You dont have to do multiple joins in seperate queries, you can do as many joins in one query as you see fit!

The thing you have to think about is mixing left and right joins, to which I always say... pretend the right doesnt exist and use a left always. It is much more clear to our western left to right thinking minds....

In particular with inner joins you should never reach a limit of joining tables....

thanks,

what is the structure of that? how do I connect them...

Exactly like you are doing now:
SELECT Query1.movie_title, Query1.movie_year, Query1.genre_id, tbl_users_comments.comment
FROM Query1
LEFT JOIN tbl_users_comments ON Query1.movie_id=tbl_users_comments.movie_id
Left join table2 on query1.somefield = table2.somefield
left join table3 on tbl_users_comments.ThisField = table3.Thatfield
Etc...

Or in the designer, simply drag and drop the fields... .like you are used to.

Yeh, that's the thing. When I do that in the designer I get the error about ambiguous joins

If you get that error is because you are not applying my rule, only use left joins... or inner... once you go outer join you cannot go back
- Inner join
> left
< Right

A - B - C
A > B > C
A < B < C
A - B > C
A < B - C
These are valid combinations....

BUT...
A > B - C
A > B < C
and/or different combinations are going to give you this error.

In the query builder if you do an outer join you see an arrow on the line, always keep the arrow going one way...

Nice explanation, thank you I am trying now…. ;-)

Got it....

SELECT tbl_movies.movie_title, tbl_movies.movie_year, tbl_movies.movie_imdb, tbl_movies_genre.movie_genre, tbl_users_comments.comment FROM (tbl_movies LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id) LEFT JOIN tbl_movies_genre ON tbl_movies.movie_id = tbl_movies_genre.movie_id;

Thanks again

That would be:
SELECT
tbl_movies.movie_title
, tbl_movies.movie_year
, tbl_movies.movie_imdb
, tbl_movies_genre.movie_genre
, tbl_users_comments.comment
FROM (tbl_movies
LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id)
LEFT JOIN tbl_movies_genre ON tbl_movies.movie_id = tbl_movies_genre.movie_id;

Please format code if you put it on the forum, but glad you got it... Hope you understand the logic now of why things fail and i.e.
SELECT
tbl_movies.movie_title
, tbl_movies.movie_year
, tbl_movies.movie_imdb
, tbl_movies_genre.movie_genre
, tbl_users_comments.comment
FROM (tbl_movies
LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id)
INNER JOIN tbl_movies_genre ON tbl_movies.movie_id = tbl_movies_genre.movie_id;

Does work , while
SELECT
tbl_movies.movie_title
, tbl_movies.movie_year
, tbl_movies.movie_imdb
, tbl_movies_genre.movie_genre
, tbl_users_comments.comment
FROM (tbl_movies
LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id)
RIGHT JOIN tbl_movies_genre ON tbl_movies.movie_id = tbl_movies_genre.movie_id;

Would break??

Uuu, your code looks much nicer and more readible.. I will do that as wel form now. Sorry and thanks again

Hope you understand the logic now of why things fail and i.e.
...
Does work , while
...
Would break??

Well do you?

I think I do....

can you not do it visually - its MUCH easier to see whats going on

How can I change then something like that: It works but it has Right and Left in the syntax and I think I can move things around to achieve the same result but only using LEFT, not sure how to do it though....

SELECT
tbl_movies.movie_title,
tbl_movies_genre.movie_genre,
tbl_users_comments.comment,
tbl_users.user_fname
FROM
(tbl_users RIGHT JOIN
(tbl_movies LEFT JOIN tbl_users_comments ON tbl_movies.movie_id = tbl_users_comments.movie_id)
ON tbl_users.user_id = tbl_users_comments.user_id)
LEFT JOIN tbl_movies_genre ON tbl_movies.movie_id = tbl_movies_genre.movie_id
WHERE tbl_movies.movie_id=1;

Not sure if I formated it correctly ;-(