5 table join

This might also work for you

SELECT *
FROM Table1
INNER JOIN
(
SELECT PartNumber
FROM Table2
UNION
SELECT PartNumber
FROM Table3
UNION
SELECT PartNumber
FROM Table 4
UNION
SELECT PartNumber
FROM Table 5
)
AS PARTSTABLE ON Table1.PartNumber = PARTSTABLE.PartNumber

But here's the thing...

I want Quantity2 to be 1 column in the result.
How do I pull Quantity2 (Table1) and Quantity (Table2-5) as only 2 columns?

Result

PartNumber       Quantity        Quantity2           Cost

SELECT Table1.PartNumber, Table1.Quantity2, COALESCE(Table2.Quantity, Table3.Quantity, Table4.Quantity, Table5.Quantity) AS Quantity, Table1.Cost
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.PartNumber = Table2.PartNumber
                    LEFT OUTER JOIN Table3 ON Table1.PartNumber = Table3.PartNumber
                    LEFT OUTER JOIN Table4 ON Table1.PartNumber = Table4.PartNumber
                    LEFT OUTER JOIN Table5 ON Table1.PartNumber = Table5.PartNumber

SELECT Table1.PartNumber,Table1.PartNumber,PARTSTABLE.Quantity2, Table1.Cost
FROM Table1
INNER JOIN
(
SELECT PartNumber,Quantity2
FROM Table2
UNION
SELECT PartNumber,Quantity2
FROM Table3
UNION
SELECT PartNumber,Quantity2
FROM Table 4
UNION
SELECT PartNumber,Quantity2
FROM Table 5
)
AS PARTSTABLE ON Table1.PartNumber = PARTSTABLE.PartNumber

SELECT
  Table1.PartNumber,
  COALESCE(Table2.Quantity,Table3.Quantity,Table4.Quantity,Table5.Quantity) AS Quantity,
  Table1.Quantity2,
  COALESCE(Table2.Cost,Table3.Cost,Table4.Cost,Table5.Cost) AS Cost
FROM
  Table1 LEFT OUTER JOIN Table2 ON Table1.PartNumber = Table2.PartNumber
                    LEFT OUTER JOIN Table3 ON Table1.PartNumber = Table3.PartNumber
                    LEFT OUTER JOIN Table4 ON Table1.PartNumber = Table4.PartNumber
                    LEFT OUTER JOIN Table5 ON Table1.PartNumber = Table5.PartNumber

Slight type, first line should be
SELECT Table1.PartNumber,Table1.Quantity,PARTSTABLE.Quantity2, Table1.Cost

I tried this...

SELECT Terry.PartNumber, Terry.Quantity as FinalCount, COALESCE(imPiawipiof.Quantity, imPiawiproic.Quantity, imPifin.Quantity, imPiwip.Quantity) AS SMFQty, COALESCE(imPiawipiof.Cost, imPiawiproic.Cost, imPifin.Cost, imPiwip.Cost) AS Cost
From Terry Left Outer join imPiawipiof on Terry.PartNumber = imPiawipiof.ShopOrder
           Left outer join imPiawiproic on Terry.PartNumber = imPiawiproic.RepOrder
           Left Outer join imPifin on Terry.PartNumber = imPifin.ROIC
           Left Outer join imPiwip on Terry.PartNumber = imPiwip.RepOrder


It's sending NULL values back for the two COALESCE columns

Sorry Lee...I guess I should have been more detailed in my question. I was just trying to get an idea of how this works.
In what I'm actually trying to do the Part Number is actually not called PartNumber in all 5 tables (as you can see from my
above query) Sorry for the confusion.

Try this.  Trust me, this questions beggs for a UNION.

SELECT Terry.PartNumber,Terry.Quantity AS FinalCount, PART.Quantity, PART.Cost
INNER JOIN
(
SELECT ShopOrder AS Order,Quantity,Cost
FROM imPiawipiof
UNION
SELECT RepOrder AS Order,Quantity,Cost
FROM imPiawiproic
UNION
SELECT ROIC AS Order,Quantity,Cost
FROM imPifin
SELECT RepOrder AS Order,Quantity,Cost
FROM imPiwip
)
AS PART ON Terry.PartNumber = PART.Order

Lee...from your last post...


Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'INNER'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Order'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Order'.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'Order'.
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Order'.

yep, sudheeshthegreat caught me on that last Union, add that and you should be gold....I guess this is why my code never compiles on the first try.

It's working now, but it's not returning any records. Can you explain what it is doing exactly?
What does PART.[Order] equate to?

Basically it merges the 4 tables into one (Renaming the relevent fields to Order), then does an INNER JOIN between your main table, and the new generated table.  Are you sure that the Terry.PartNumber field EXACTLY matches the ShopOrder,RepOrder,ROIC fields?  try this as a test

SELECT *
FROM Terry
INNER JOIN imPiawipiof ON imPiawipiof.ShopOrder = Terry.PartNumber

If that returns no records, that means none of the ShopOrder fields match any of the PartNumber fields.  If that's the case we may have to massage the fields a little bit to get them to match property.