三表查询
表1 goods --记录商品名 goodid 为主键
goodid goodname
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
表2 shopgoods
goodid shopid goodnum
1 1 15
2 1 19
3 1 18
4 1 21
6 1 17
goodsid为外键引用
表3 whogoods
goodid whoid price
1 1 3.00
3 1 4.00
4 1 2.50
goodsid为外键引用
现在要求找出表二中数量少于20的商品名和id（满足shopid=1）,同时显示该商品记录在表三中的价格（满足whoid=1）,如果表三中没有该商品,则显示价格为0.00,在线等。
求答案。
[解决办法]
select a.goodid,a.goodnum ,
case when b.price is null then 0.00 else b.price end as price
from
(select goodid, shopid ,goodnum from shopgoods where shopid=1 and goodnum<20) a
left join whogoods b on a.goodid = b.goodid and b.whoid = 1
select a.goodid,b.goodname,isnull(c.price,0) price from shopgoods a join goods b on a.goodid=b.goodid
left join whogoods c on a.goodid=c.goodid
where a.shopid=1 and c.whoid=1
