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?
July 1, 2021 at 5:08 pm
Sounds like a join. But without knowing more about the data and table structure, it's difficult to help any more than that.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 1, 2021 at 5:19 pm
Based on the limited info we have so far, the JOINs would look something like this:
SELECT *
FROM dbo.main_table mt
LEFT OUTER JOIN dbo.other_table1 ot1 ON ot1.WOQty = mt.WONum
LEFT OUTER JOIN dbo.other_table2 ot2 ON ot2.WOQty = mt.WONum
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 1, 2021 at 5:30 pm
I am always not sure what additional info you guys are looking for.
Main Table tblJDEWOData2
BusinessUnit char(30)
PartNum char(30)
WONum int
TransDate datetime
TransQty int
Table 1 tblHistory
HistoryID int
WONum int
WOQty int
Table 2 tbl WONumWOQty
WONum int
WOQty int
Output
BusinessUnit PartNum WONum TransDate TransQty WOQty
July 1, 2021 at 5:56 pm
So my response did not help you at all?
If you need to return data from the non-main tables conditionally, based on whether the WONum appears in each table or not, just specify those conditions and I will adjust the code accordingly, if you want me to and it would help you.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 1, 2021 at 6:05 pm
Scott,
I get no values returned from either of the other tables even though I do when I query each separately...
I do get both sets if I change the Query to:
SELECT mt.BusinessUnit, mt.PartNum, mt.WONum, mt.TransDate, mt.TransQty, ot1.WOQty, ot2.WOQty AS WOQty2
FROM tblJDEWOData2 AS mt LEFT OUTER JOIN
tblWONumWOQty AS ot1 ON ot1.WONum = mt.WONum LEFT OUTER JOIN
tblHistory AS ot2 ON ot2.WONum = mt.WONum
ORDER BY mt.TransDate
But then of course, I have two columns for WOQty
Assuming you don't want to return the second if the first gets a match -- you never really stated exactly -- then do this:
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
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 1, 2021 at 7:52 pm
Scott,
Thanks for the help. That's exactly what I needed. Now I just have to find out why there's so many NULLs in my source tables. There's always something...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply