How do you join OpenQuery onto another Select statement?

Question

I have written statement using OpenQuery and now want to join it onto my another query, so that when I run the view it returns a table with records from both selects. How would I go about this?

These are the queries I currently have:

SELECT  I.Invoice_ID,
        I.Invoice_Num,
        I.SO_ID,
        I.SO_Num, 
        O.AccountNumber, 
        I.ShipZip               AS PostCode,
        I.RequestedDeliveryDate AS DeliveryDate,
        I.AddDate,
        I.AddUser,
        O.OrgName,
        I.[Status]
    FROM dbo.Org O  
    INNER JOIN dbo.Invoice I ON O.Org_ID = I.Org_ID

(SELECT * FROM OPENQUERY ([TEST-SQL-TEST-02], 'SELECT GoodsOutTest, LoadRef FROM TestWMS.dbo.GoodsTest')

Answer 1

Why not just literally JOIN to it?

SELECT I.Invoice_ID,
       I.Invoice_Num,
       I.SO_ID,
       I.SO_Num,
       O.AccountNumber,
       I.ShipZip AS PostCode,
       I.RequestedDeliveryDate AS DeliveryDate,
       I.AddDate,
       I.AddUser,
       O.OrgName,
       I.[Status],
       GT.GoodsOutTest
FROM dbo.Org O
     INNER JOIN dbo.Invoice I ON O.Org_ID = I.Org_ID
     INNER JOIN OPENQUERY ([TEST-SQL-TEST-02], 'SELECT GoodsOutTest, LoadRef FROM TestWMS.dbo.GoodsTest') GT ON GT.LoadRef = I.Ref; --Guess ON clause, correct to be appropriate for your data.