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')
Advertisement
Answer
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.