4表查询问题

[artak]

select [user].userName,shop.shopname,role.rolename from [user],shop,userrole,role where [user].userid='qq'
and [user].shopid = shop.shopid and userrole.roleid=role.roleid and [user].userid=userrole.userid
上面的语句是4表查询,目的是将userName,shopName,rolename选择出来,还有其他办法吗?听过有个上面join
比如
select [user].userName,shop.shopnamefrom [user]
join shop on [user].userid='qq' and [user].shopid = shop.shopid
可是我不懂怎么连接3个以上的表

[dawugui]

你的方法要快过左连接left join
四表连接方法如下:

select a.*,b.*,c.*,d.* from a 
left join b on a.id = b.id
left join c on a.id = c.id
left join d on a.id = d.id
and a.userid='qq' 

[Limpire]

select [user].userName, shop.shopname, role.rolename from [user]
join shop on [user].shopid=shop.shopid
join userrole on [user].userid=userrole.userid
join role on userrole.roleid=role.roleid
--join another table
where [user].userid='qq'

[artak]

用left join 会有空值,用join就没有了