I have a Main table that contains a field 'WONum'. A similarly named filed exists in two other tables. Those two other tables also contain the filed that I want to JOIN or UNION with my main table. That field is named 'WOQty'. So what I need is to JOIN my Main table with the WOQty from either of the other tables. Is that a JOIN or a UNION or something else entirely?
That would normally be a JOIN. You could use UNION if and only if you wanted only that column from both tables. If you want other columns from either table, you need to use JOIN.
Scott on another forum helped me with this answer:
SELECT mt.BusinessUnit, mt.PartNum, mt.WONum, mt.TransDate, mt.TransQty, ISNULL(ot1.WOQty, ot2.WOQty) AS WOQty
FROM tblJDEWOData2 AS mt LEFT OUTER JOIN
tblWONumWOQty AS ot1 ON ot1.WONum = mt.WONum LEFT OUTER JOIN
tblHistory AS ot2 ON ot1.WONum IS NULL AND ot2.WONum = mt.WONum
ORDER BY mt.TransDate