SQL where with Union and Left

Question

Is it possible to add criteria in [ID] and not in [TypeID] in Left Join?

 SQL = "SELECT " & _
          "ADate As NewDate, " & _
          "tblA.TypeID as ID, " & _
          "tblAB.TypeControl as ControlID " & _
          "FROM tblA " & _
               "LEFT OUTER JOIN tblAB " & _
               "ON tblAB.TypeID = tblA.TypeID " & _
               "WHERE tblA.TypeID = " & Counter & " " & _     => Delete this one.
       "UNION ALL SELECT " & _
          "BDate As NewDate, " & _
          "tblB.TypeID as ID, " & _
          "tblAB.TypeControl as ControlID " & _
          "FROM tblB " & _
               "LEFT OUTER JOIN tblAB " & _
               "ON tblAB.TypeID = tblB.TypeID " & _
               "WHERE tblB.TypeID = " & Counter & " " & _     => Delete this one.
       ===
       and place one WHERE on ID here
       "WHERE ID = " & Counter & " " & _    => Like this one. But I am getting an error.
       ===
       "ORDER BY NewDate;"

Delete the two WHERE from tblA and tblB. Add one in ID in the end. And create this one.

 SQL = "SELECT " & _
          "ADate As NewDate, " & _
          "tblA.TypeID as ID, " & _
          "tblAB.TypeControl as ControlID " & _
          "FROM tblA " & _
               "LEFT OUTER JOIN tblAB " & _
               "ON tblAB.TypeID = tblA.TypeID " & _
       "UNION ALL SELECT " & _
          "BDate As NewDate, " & _
          "tblB.TypeID as ID, " & _
          "tblAB.TypeControl as ControlID " & _
          "FROM tblB " & _
               "LEFT OUTER JOIN tblAB " & _
               "ON tblAB.TypeID = tblB.TypeID " & _
               "WHERE tblB.TypeID = " & Counter & " " & _
       "WHERE ID = " & Counter & " " & _
       "ORDER BY NewDate;"

Thank you in advance.

Answer 1

You can check for ID's only once by wrapping your entire query in a subquery, and then check for IDs in the outer query, e.g.:

SELECT * FROM (
    SELECT " & _
      "ADate As NewDate, " & _
      "tblA.TypeID as ID, " & _
      "tblAB.TypeControl as ControlID " & _
      "FROM tblA " & _
           "LEFT OUTER JOIN tblAB " & _
           "ON tblAB.TypeID = tblA.TypeID " & _
    "UNION ALL SELECT " & _
      "BDate As NewDate, " & _
      "tblB.TypeID as ID, " & _
      "tblAB.TypeControl as ControlID " & _
      "FROM tblB " & _
           "LEFT OUTER JOIN tblAB " & _
           "ON tblAB.TypeID = tblB.TypeID "
) WHERE ID =  " & Counter & "

However, depending on how the database engine optimizes this, it might take longer to execute. I recommend you don't do this, and leave your query as-is.

(Also, I leave the quotes mess to you, since your question shouldn't have these anyway).