SELECT 1 AS 仓库ID, (select top 1 仓库名称 from 仓库表 where 仓库ID=1) AS 仓库名称, A.商品ID, A.商品名称, iif(isnull(B.库存数量),0,B.库存数量) AS 库存数量
FROM 商品表 AS A LEFT JOIN (select * from 库存表 where 仓库ID=1) AS B ON A.商品ID=B.商品ID
SELECT
1 AS 仓库ID,
(select top 1 仓库名称 from 仓库表 where 仓库ID=1) AS 仓库名称,
A.商品ID,
A.商品名称,
iif(isnull(B.库存数量),0,B.库存数量) as 库存数量
FROM 商品表 AS A LEFT JOIN 库存表 AS B ON A.商品ID=B.商品ID
WHERE B.仓库ID=1;
SELECT
1 AS 仓库ID,
(select top 1 仓库名称 from 仓库表 where 仓库ID=1) AS 仓库名称,
A.商品ID,
A.商品名称,
iif(isnull(B.库存数量),0,B.库存数量) as 库存数量
FROM 商品表 AS A LEFT JOIN 库存表 AS B ON A.商品ID=B.商品ID
WHERE B.仓库ID=1;
--或
SELECT
1 as 仓库ID,
(select top 1 仓库名称 from 仓库表 where 仓库ID=1) as 仓库名称,
A.商品ID,
A.商品名称,
nz(B.库存数量,0) as 库存数量
FROM 商品表 AS A LEFT JOIN 库存表 AS B ON A.商品ID = B.商品ID
WHERE B.仓库ID=1
SELECT
1 as 仓库ID,
(select top 1 仓库名称 from 仓库表 where 仓库ID=1) as 仓库名称,
A.商品ID,
A.商品名称,
nz(B.库存数量,0) as 库存数量
FROM 商品表 AS A LEFT JOIN 库存表 AS B ON A.商品ID = B.商品ID
WHERE B.仓库ID=1