How MySQL Select OpenQuery Not In Statement

Question

Below statement will return values from OpenQuery(MYSQL).

Select * From OpenQuery(MYSQL, 'Select * From ResearchRpt') a
Where Not Exists (Select * From dbo.ResearchRpt where Id_report = a.Id_report)

I want to reversed it where the result values should be came dbo.ResearchRpt

Thank.

Perhaps you need to tag this with something else? I don't think it's really a MySQL question... — eggyal, May 02 '12 at 08:06
According to your query, you want openquery records which do not have a corresponding ResearchRpt. So what is your desired output? Could you post some sample input/output? — Chetter Hummin, May 02 '12 at 08:06
Sorry guy this sql statement was from MSSQL 2005. I want to get the result(output) where records are not exist in MSSQL not from MYSQL. — SiChiPan, May 02 '12 at 08:20
You are looking for records which not exist in MSSQL but exist in MYSQL. Now you want to display the columns of MSSQL table, it means the result will have null value for all columns because no record will be found respective to MYSQL in MSSQL. — Romil Kumar Jain, May 02 '12 at 08:40
Table structure in MSSQL and MYSQL are the same, I just want to find out which data are not exist in MSSQL by comparing this two tables. From my MSSQL statement provided here it will return result from MYSQL not MSSQL. Can somebody provide me the valid statement to do this. — SiChiPan, May 02 '12 at 09:14

Andriy M · Accepted Answer · 2015-01-13T16:27:20.250

2

Your query uses an anti-join. An anti-join can be implemented with LEFT JOIN + WHERE IS NULL just as well as with NOT EXISTS. So, convert your script to the LEFT JOIN version, swap the sides, and you are done:

SELECT ms.*
FROM dbo.ResearchRpt ms
  LEFT JOIN OPENQUERY(MYSQL, 'SELECT * FROM ResearchRpt') my
    ON my.Id_report = ms.Id_report
WHERE my.Id_report IS NULL

edited Jan 13 '15 at 16:27

answered May 02 '12 at 21:50

Andriy M

76,112
17
94
154

Thank Andriy, it what I want. – SiChiPan May 03 '12 at 01:18

score 1 · Answer 2 · edited Jun 11 '12 at 22:46

1

-- Only exists

SELECT ms.*, my.Id_report
FROM dbo.ResearchRpt ms
LEFT JOIN OPENQUERY(MYSQL, 'SELECT * FROM ResearchRpt') my
ON my.Id_report = ms.Id_report
WHERE my.Id_report IS NOT NULL

edited Jun 11 '12 at 22:46

Mariusz Jamro

30,615
24
120
162

answered Jun 11 '12 at 22:43

Tiago Rosa

11
1

How MySQL Select OpenQuery Not In Statement

2 Answers2