Question

SQL Server查询多个表内容

这是表内容:
(BOM) (Product)
(Order)
BOMNo BOMName ProductNo OrderNo | ProductNo ProductName | OrderNo
OrderName
1 q null 1 | 1 w | 1 e
2 a 1 null | 2 s | 2 d
3 z null 2 |

想要显示效果是:
BOMNo BOMName ProductName OrderName
1 q null e
2 a w
null
3 z null d

Answer 1

select a.bomno,a.bomname,
case when b.productname is null then 'null' else b.productname end as productname,
case when c.ordername is null then 'null' else c.odername end as ordername
from bom a left join product b on (a.productno=b.productno) left join order c on (a.orderno=c.orderno)

Answer 2

select BOMNo,BOMName,ProductName,OrderName from BOM left join Product on BOM.ProductNo=Product.ProductNo left join Order on BOM.OrderNo=Order.OrderNo

Answer 3

select BOMNo,BOMName,ProductName,OrderName from BOM,Product,Order where BOM.ProductNo=Product.ProductNo and BOM.OrderNo=Order.OrderNo

Answer 4

select a.BOMNo,a.BOMName,b.ProductName,c.OrderName from BOM a
left outer join Product b on a.ProductNo = b.ProductNo
left outer join Order c on a.OrderNo = c.OrderNo