MyNote : LEFT OUTER JOIN with WHERE string, under MYSQL
[EXAMPLE]
SELECT MAIN.CLIPID, MAIN.ID, COUNT(DISTINCT DETAIL.CLIPDTID) PAGE_SUM, SUM(DETAIL.TAG_AMOUNT) TAG_SUM
FROM MAIN
LEFT OUTER JOIN DETAIL ON MAIN.CLIPID=DETAIL.CLIPID
WHERE MAIN.ID='54321'
GROUP BY DETAIL.CLIPID
where 子句要寫在outer join 的後面
count 裡面的欄位據說要有 distinct 否則會有 M*N 的效應
以上是發生在 DETAIL裡面是零筆的狀態下，需要LEFT OUTER JOIN 才可以合乎各種狀況
SELECT MAIN.CLIPID, MAIN.ID, COUNT(DISTINCT DETAIL.CLIPDTID) PAGE_SUM, SUM(DETAIL.TAG_AMOUNT) TAG_SUM
FROM MAIN
LEFT OUTER JOIN DETAIL ON MAIN.CLIPID=DETAIL.CLIPID
WHERE MAIN.ID='54321'
GROUP BY DETAIL.CLIPID
where 子句要寫在outer join 的後面
count 裡面的欄位據說要有 distinct 否則會有 M*N 的效應
以上是發生在 DETAIL裡面是零筆的狀態下，需要LEFT OUTER JOIN 才可以合乎各種狀況
留言