How to add a where clause conditioning another database to OpenQuery linked server?-CodePudding

I am learning about linked server in sql server and I linked the oracle database in sqlserver and ran a query successfully against the oracle linked database as below:

SELECT *
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients')

I have a table in sql server as well that I want to use that data in a where clause of OPENQUERY above but do not know how. Here is the query:

Select ID from PatientTable

so based on the above sql queries, I want something like this:

SELECT *
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients')
where "--ID in OPENQUERY above" IN (Select ID from PatientTable)

SELECT *
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients where ID in (--Select ID from PatientTable)')

Update:

I tested the resolution Stu provided and it almost worked but I cannot call the column field in openquery within the external where clause

Screenshot of the error

CodePudding user response：

Try an exists correlation

select *
from OpenQuery(DB_ORCL,'select Name, ID from OdataLink.patients') q
where exists (
  select * from dbo.PatientTable p
  where p.Id = q.Id
);