Display all records from table 1 regardless of no match from table 2 upon joining table-CodePudding

TABLES:

OBJECTIVE: Display all records from ITEM table regardless of no match from INVENTORY table.

CODE:

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

RESULT:

i already tried LEFT, RIGHT, LEFT OUTER, RIGHT OUTER JOIN but still the same result comes out.

QUESTION: may i know how can i achieve the result same as in my object. thank you.

CodePudding user response：

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

in order for me to filter item table before joining inventory table.

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

Thanks to @HansUp!