4表查询有关问题

4表查询问题
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个以上的表

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

SQL code

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'

------解决方案--------------------

SQL code

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'

相关推荐