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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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