請問各位

我的資料(以下是舉例)
master
no money date
a001 1000 2017/08/17
a002 1000 2017/08/17

detail
no counts date
a002 50 2017/08/17

我要統計出以上記錄
no money counts
a001 1000 0
a002 1000 50

我使用以下語法
select a.no, a.money, b.counts
from
(select master.no, sum(master.money) as money from master where master.date ='2017/08/17' group by master.no) a,
(select detail.no, sum(detail.counts) as counts from detail where detail.date='2017/08/17' group by detail.no) b
where
b.no = a.no

但這樣做的結果, 因為 a001 沒有對應的 detail, 所以答案是如下, a001並不會出現,
no money counts
a002 1000 50

當然整個完整的語法是比上面複雜多, 所以請教有沒有其他的方法可以達到我想要的結果?
謝謝!

P.D.大~
請試試以下:
(mssql)

Select A.no, A.money, IsNull(B.counts, 0) Counts
From A_MASTER A
Left Join (Select no, Sum(counts) counts From A_DETAIL Group by no) B on A.no = B.no
Where 1=1




===================引 用 P.D. 文 章===================
請問各位

我的資料(以下是舉例)
master
no money date
a001 1000 2017/08/17
a002 1000 2017/08/17

detail
no counts date
a002 50 2017/08/17

我要統計出以上記錄
no money counts
a001 1000 0
a002 1000 50

我使用以下語法
select a.no, a.money, b.counts
from
(select master.no, sum(master.money) as money from master where master.date ='2017/08/17' group by master.no) a,
(select detail.no, sum(detail.counts) as counts from detail where detail.date='2017/08/17' group by detail.no) b
where
b.no = a.no

但這樣做的結果, 因為 a001 沒有對應的 detail, 所以答案是如下, a001並不會出現,
no money counts
a002 1000 50

當然整個完整的語法是比上面複雜多, 所以請教有沒有其他的方法可以達到我想要的結果?
謝謝!

編輯記錄

感謝提供, 不過我試的結果一直不成功, 這是我原本語法改過來的

select a.DEPTNO, a.SPC0, sum(a.ARS) as ARS, sum(a.CASH) as CASH,
sum(a.VISA) as VISA, sum(a.PAYED) as PAYED, sum(a.DEPOSIT) as DEPOSIT
/*, coalesce(b.COUNTS, 0) as COUNTS*/
from DP000 a

/*
left join
(select DEPTNO, SPC0, sum(COUNTS) as COUNTS from DP001
where DP001.FORMTYPE="1" and DP001.ISCONFIRM="Y" and
DP001.FORMDATE>= '20170813' and DP001.FORMDATE<= '20170831' and DP001.DEPTNO="A"
group by DP001.DEPTNO, DP001.SPC0) b
on (a.DEPTNO = b.DEPTNO and a.SPC0 = b.SPC0)
*/
where a.FORMTYPE="1" and a.ISCONFIRM="Y" and
a.FORMDATE>= '20170813' and a.FORMDATE<= '20170831' and a.DEPTNO="A"
group by a.DEPTNO, a.SPC0

我用的是firebird, 沒有isnull, 但網路找得類似的coalesce
/* .. */ 如果移除 remark 執行就會出錯 sql error -104 (不管有沒有加 where 語法)
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
如果把 /* .. */ mark起來, 執行ok, 可以顥示 master 的資料(但沒有detail的counts)
還在找正確的用法中...

在底下網頁看到用Case When方式達到類似IsNull效果
CanVote = case
when Age >= 18 then 'Yes'
when Age < 18 then 'No'
else 'Unsure'
end;
https://firebirdsql.org/manual/nullguide-testing.html

查了一些文獻, 試出來了

select a.DEPTNO, a.SPC0, sum(a.ARS) as ARS, sum(a.CASH) as CASH,
sum(a.VISA) as VISA, sum(a.PAYED) as PAYED, sum(a.DEPOSIT) as DEPOSIT
, coalesce(sum(b.COUNTS), 0) as COUNTS
from DP000 a

left join
(select DP001.DEPTNO, DP001.SPC0, DP001.COUNTS from DP001
where DP001.FORMTYPE="1" and DP001.ISCONFIRM="Y" and
DP001.FORMDATE>= "20170813" and DP001.FORMDATE<= "20170831" and DP001.DEPTNO="A") b
on (a.DEPTNO = b.DEPTNO and a.SPC0 = b.SPC0)

where a.FORMTYPE="1" and a.ISCONFIRM="Y" and
a.FORMDATE>= "20170813" and a.FORMDATE<= "20170831" and a.DEPTNO="A"
group by a.DEPTNO, a.SPC0

感謝所有提供協助的大大...
會再進行更多記錄的測試, 尚不知這樣的語法是否能符合所想要的結果

實測資料庫的結果符合我想要答案, 而且
coalesce(mydata, 0) 也可以取代 isnull 的用法
又多學習到一種 sql 用法, 感謝大大提供

select m.no,m.money,isnull(d.counts,0) from master m left join detail d on m.no=b.no

大概是這樣吧.

===================引 用 P.D. 文 章===================
請問各位

我的資料(以下是舉例)
master
no money date
a001 1000 2017/08/17
a002 1000 2017/08/17

detail
no counts date
a002 50 2017/08/17

我要統計出以上記錄
no money counts
a001 1000 0
a002 1000 50

我使用以下語法
select a.no, a.money, b.counts
from
(select master.no, sum(master.money) as money from master where master.date ='2017/08/17' group by master.no) a,
(select detail.no, sum(detail.counts) as counts from detail where detail.date='2017/08/17' group by detail.no) b
where
b.no = a.no

但這樣做的結果, 因為 a001 沒有對應的 detail, 所以答案是如下, a001並不會出現,
no money counts
a002 1000 50

當然整個完整的語法是比上面複雜多, 所以請教有沒有其他的方法可以達到我想要的結果?
謝謝!