[知識交流] 關於ACCESS 出現 Join expression not supported 的問題

 先說明我遇到一個狀況, SQL 語法如下

select d1.*
from history_tmp d1 left outer join history_tmp d2 on  d2.period = 1154
where d1.period = 1156
;
執行後出現錯誤訊息
SQLSTATE = S1000
[Microsoft][ODBC Microsoft Access Driver] Join expression not supported.

記得之前有在 ACCESS 上寫過 left outer join 的語法它是可以支援的,

結果一段時間沒回來, 再突然寫一個新需求就不行了? 把我整的有點曚.

文章最後我會把原因和這次的解決方式做一說明,

只是在這裡我先分享一下排查邏輯, 因為單一問題被解決雖然是現在的重點, 但解決問題的方法也很重要因為平常工作上會遇到問題是很正常的事, 

有時參考網路上的分享之餘還是要有自己的判斷, 像我這次的例子就是這樣,

(如果要直接看最後的結果請直接拉到最後面)

------------------- 過程 ------------------------------------

首先我先想辦法找到回到 OK的狀態, 才能重新一步步找到錯誤原因和正確的路,

就好像迷路時解決的方向是先看可不可以回到之前沒迷路的路口, 然後才能再重新選一個路口前進的意思, 看是在哪一條巷口選錯了方向.

所以我產出一個最應該沒問題的語法如下

*****語法A 
select d1.*
from history_tmp d1, history_tmp d2 
where 
d1.period = 1156 and d2.period = 1154
;
結果 => 成功

這種寫法是早期的寫法, 其效果等於是 inner join, 就是必需2邊table 都要有資料才會出現一筆,

隨著SQL語法的發展以及新加入者的學習, 針對對等聯結或左右聯結官方提出了建議要用 inner 或 left 或 right 的寫法, 

(但同時又向下相容舊的寫法, 以前是在等號的左邊或右邊加上星號來代表左右連結)

所以只要出現問題, 我第一時間就是改用官方要求的標準最新寫法來改寫,

在語法A沒問題後, 因為的目的最後是要用新寫法來完成(left join), 我用官方方改寫如下

*****語法B 
select d1.*
from history_tmp d1 inner join history_tmp d2 on d2.period = 1154
where d1.period = 1156
;
結果 => 失敗 Join expression not supported.

走到這裡我突然懷疑難道之前印象是錯的?  ACCESS 不支援左右連結的新寫法? 這不可能啊! 

上網查了一下肯定是有支援的,

所以我決定用一樣的標準寫法, 但是針對左右 table 照平常的狀況選擇用二個不同的table,

(有注意到我出問題的這個需求所使用的table 左右 table 都是同一個嗎? )

*****語法C 
select 
d1.*, 
d2.zone1
from history_d d1 inner join history_tmp d2 on d2.period=d1.period
where 
d1.period = 1154
;
結果 => 成功

這個成功表示, ACCESS 標準寫法是可以的, 所以我小小前進一步, 把它改成我要的 left join 看看

*****語法D 
select 
d1.*, 
d2.zone1
from history_d d1 left join history_tmp d2 on d2.period=d1.period
where 
d1.period = 1154
;
結果 => 成功

到此整個問題點開始轉向, 問題會不會出在我的左右二邊使用了相同一個 table 所致?

所以我打算從這個OK的語法開始, 朝向最後我要的那個需求修改, 

然後看看它是錯在哪一個階段

*****語法E
select 
d1.*, 
d2.zone1
from history_d d1 left join history_tmp d2 on d2.period=1156
where 
d1.period = 1154
;
結果 => 失敗 Join expression not supported.

於是我觀察了上一個語法和現在這個錯誤的語法, 想了一下它的差別以為為什麼

D: d2.period=d1.period   <= OK

E: d2.period=1156            <= 不OK

發現E少給了「d1,d2這2個table的聯結條件」....

當然在我需求下我這二個table 就是不要有關聯的(我只是想拉出無key值相關的參考資料),但眼下過不去就是要想辦法,

於是我硬著頭皮加上 d1 的條件

*****語法F
select 
d1.*, 
d2.zone1
from history_d d1 left join history_tmp d2 on d2.period=1156 and d1.period = 1154
where 
d1.period = 1154
;
結果 => 失敗 Join expression not supported.

還是錯? 好, 

但語法D成功表示方向應該是對的, 只是它可能更要求, 要表達出二個table 之間的關係才行, 

所以我使用語法D, 再加上一個小條件, 如下

*****語法G
select 
d1.*, 
d2.zone1
from history_d d1 left join history_tmp d2 on  d2.period = d1.period and d2.period = 1156
where 
d1.period = 1154
;
結果 => 失敗 Join expression not supported.

到這裡是我最崩潰的地方, 因為只是加上了一個條件就失敗了, 這不合理啊!

and d2.period = 1156

這時 google 大神來幫忙派上用場, 

因為我用關鍵字查 「ACCESS中不支持多个left join解决方案」

https://blog.csdn.net/xiao_rory/article/details/8135370

有一些人提到ACCESS 不支援二個以上的 left join , 必需加上括號....

雖然我的例子裡只有一個 left join 但我已有體會程式是很笨的,

要加上括號表示它在解譯的時侯分不清處誰要先誰要後, 

如果是 MS SQL肯定沒問題分得清, 但現在面對ACCESS 就要配合照它的,

所以我想了一下我只是再加一個條件它就報錯了, 難道它一次只認識一個條件的表達方式,

那我二個條件用括號把它括起來, 它會不會就懂了? 於是有了下面

*****語法H
select 
d1.*, 
d2.zone1
from history_d d1 left outer join history_tmp d2 on  (d2.period = d1.period and d2.period = 1156)
where 
d1.period = 1154
;
結果 => 成功

最後依照上面的學習到的眉角, 我成功直接改寫原本失敗的語法如下

*****語法I

select d1.*

from history_tmp d1 left outer join history_tmp d2 on (d2.period<>d1.period and  d2.period = 1154)

where d1.period = 1156

;

結果 => 成功

說明:

1. ACCESS 在關聯二個table時, 一定要明確寫出二個table 的欄位關係, 如果沒有就是不行.
像我的例子不能寫=號, 那就寫 <>, 反正就是一定要有

2. ACCESS 的語法表達要適時的使用括號, 當有二個組合條件時, 用括號括起來, 雖然麻煩但也不傷害身體, 就加吧!

3. 在過程中查了一些資料得知連結多個 left join table也要個別加括號, 我現在只是單純連結一個 left join, 只是日常工作上要連結多個 left join 是常常需要的狀況, 所以本次的經驗對之後連結多個 left join table 也等於是打了一個預防針.

以上, 歡迎有經驗的朋友分享您的學習知識,

有時東西沒用到就不會去研究, 但每遇到一個點就從這個點再往旁邊擴散, 

學習其實是很有趣的.