在加入表時顯示表1中的所有記錄，無論表2是否匹配

表格：

目標：顯示 ITEM 表中的所有記錄，無論 INVENTORY 表中沒有匹配項。

代碼：

MYSQL = "SELECT T1.itm_id, T1.itm_name, T1.itm_category, T1.itm_cost, SUM(T2.qty) AS NewItmQty FROM ITEM AS T1 " & _
"LEFT OUTER JOIN INVENTORY AS T2 ON T1.itm_id = T2.itm_id " & _
"WHERE T2.storage_loc = '1ST FLR'" & _
MYSQL = MYSQL & " GROUP BY T1.itm_id, T1.itm_name, T1.itm_category, T1.itm_cost"
MYSQL = MYSQL & " ORDER BY T1.itm_category, T1.itm_name"

Set rsItem = dbInventory.Execute(MYSQL)

If rsItem.EOF Then
Else
    Do Until rsItem.EOF = True
        Set li = .lvSearchWindow.ListItems.Add(, , rsItem.Fields("itm_id"))
            li.SubItems(1) = Replace(rsItem.Fields("itm_name"), "''", "'")
            li.SubItems(2) = Replace(rsItem.Fields("itm_category"), "''", "'")
            li.SubItems(3) = FormatNumber(rsItem.Fields("NewItmQty"), 0, , vbTrue)
            li.SubItems(4) = FormatNumber(rsItem.Fields("itm_cost"), 2, , vbTrue)
            li.SubItems(5) = FormatNumber(rsItem.Fields("NewItmQty") * rsItem.Fields("itm_cost"), 2, , vbTrue)

        rsItem.MoveNext
    Loop
End If

結果：

我已經嘗試了 LEFT、RIGHT、LEFT OUTER、RIGHT OUTER JOIN，但仍然得到相同的結果。

問題：我可以知道如何獲得與我的物件相同的結果。謝謝你。

uj5u.com熱心網友回復：

MYSQL = "SELECT T1.itm_id, T1.itm_name, T1.itm_category, T1.itm_cost, SUM(T2.qty) AS NewItmQty FROM ITEM AS T1 " & _
"LEFT OUTER JOIN INVENTORY AS T2 ON T1.itm_id = T2.itm_id " & _
"WHERE T2.storage_loc = '1ST FLR' OR T2.storage_loc IS NULL" & _
MYSQL = MYSQL & " GROUP BY T1.itm_id, T1.itm_name, T1.itm_category, T1.itm_cost"
MYSQL = MYSQL & " ORDER BY T1.itm_category, T1.itm_name"

Set rsItem = dbInventory.Execute(MYSQL)

If rsItem.EOF Then
Else
    Do Until rsItem.EOF = True
        Set li = .lvSearchWindow.ListItems.Add(, , rsItem.Fields("itm_id"))
            li.SubItems(1) = Replace(rsItem.Fields("itm_name"), "''", "'")
            li.SubItems(2) = Replace(rsItem.Fields("itm_category"), "''", "'")
            li.SubItems(3) = FormatNumber(rsItem.Fields("NewItmQty"), 0, , vbTrue)
            li.SubItems(4) = FormatNumber(rsItem.Fields("itm_cost"), 2, , vbTrue)
            li.SubItems(5) = FormatNumber(rsItem.Fields("NewItmQty") * rsItem.Fields("itm_cost"), 2, , vbTrue)

        rsItem.MoveNext
    Loop
End If

為了讓我在加入庫存表之前過濾專案表。

MYSQL = "SELECT T1.itm_id, T1.itm_name, T1.itm_category, T1.itm_cost, SUM(T2.qty) AS NewItmQty FROM ITEM AS T1 " & _
"LEFT JOIN (SELECT * FROM INVENTORY WHERE T2.storage_loc = '1ST FLR') AS T2 " & _
"ON T1.itm_id = T2.itm_id " & _
"WHERE (T1.itm_id LIKE '%" & Me.txtSearchStr.Text & "%' OR T1.itm_name LIKE '%" & Me.txtSearchStr.Text & "%') " & _
MYSQL = MYSQL & " GROUP BY T1.itm_id, T1.itm_name, T1.itm_category, T1.itm_cost"
MYSQL = MYSQL & " ORDER BY T1.itm_category, T1.itm_name"

Set rsItem = dbInventory.Execute(MYSQL)

If rsItem.EOF Then
Else
    Do Until rsItem.EOF = True
        Set li = .lvSearchWindow.ListItems.Add(, , rsItem.Fields("itm_id"))
            li.SubItems(1) = Replace(rsItem.Fields("itm_name"), "''", "'")
            li.SubItems(2) = Replace(rsItem.Fields("itm_category"), "''", "'")
            li.SubItems(3) = FormatNumber(rsItem.Fields("NewItmQty"), 0, , vbTrue)
            li.SubItems(4) = FormatNumber(rsItem.Fields("itm_cost"), 2, , vbTrue)
            li.SubItems(5) = FormatNumber(rsItem.Fields("NewItmQty") * rsItem.Fields("itm_cost"), 2, , vbTrue)

        rsItem.MoveNext
    Loop
End If

感謝@HansUp！

標籤：擅长 vba 毫秒访问 加入

上一篇：如何避免MSAccess錯誤：命令或操作粘貼現在不可用

下一篇：MongoDB中的哪個關系更好？