三表查询
表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,在线等。
求答案。


[解决办法]

SQL code

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
[解决办法]
SQL code
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