Join the three tables

Question

I do not know where is my code error. I want join three tables together.

i have three tables

user_type

id_user_type(pk) | user_type

user_type_page_access

id_user_type(fk) | id_page_access(fk)

page_access

id_page_access(pk) | page | roll

i want select id_user_type, roll, page

and this my query is Sql

CREATE PROCEDURE dbo.FindRollOfUser
(
@id_user_type int
)
as
select 
u1.id_user_type,
p.page,
p.roll 
from user_type as u1 
where u1.id_user_type=@id_user_type
join user_type_page_access as u2
on u1.id_user_type=u2.id_user_type
join page_access as p
on p.id_page_access=u2.id_page_access
RETURN

thank you......

Answer 1

The previous SQL is good but base on the table name I think add some join type will be better:

 select 
 u1.id_user_type,
 p.page,
 p.roll 
 from user_type as u1 
 left join user_type_page_access as u2
 on u1.id_user_type=u2.id_user_type
 left join page_access as p
 on p.id_page_access=u2.id_page_access
 where u1.id_user_type=@id_user_type

Answer 2

Your problem is because you specify the where clause before the join. Swap the order and you should be fine:

select 
u1.id_user_type,
p.page,
p.roll 
from user_type as u1 
join user_type_page_access as u2
on u1.id_user_type=u2.id_user_type
join page_access as p
on p.id_page_access=u2.id_page_access
where u1.id_user_type=@id_user_type

For future reference, the order in which clauses should appear when writing the query is:

1. select
2. join
3. where
4. group by
5. having
6. order by

The order of evaluation of these clauses by the DBMS is not the above however.