How can I join the MS-SQL and MySQL table in a query?

Question

I want to make a connection through an MS-SQL database and MySql.

An MS-SQL query generates an index, and then I want to return all MySQL records matching the result of this query. (I could return both tables, i.e. Unfiltered data from MySQL, and then filter using Linq, but this will be inefficient, as I will pull back more data than I need.)

An MS-SQL query is executed through Linq:

var fb1 = from f in db.tl_feedbacks where f.timestamp >= new DateTime(fromYear, fromMonth, fromDay) && f.timestamp <= new DateTime(toYear, toMonth, toDay) select new {f.requestID, f.tl_feedback_score.score }; 

This will return the table as follows:

 RequestID | score ----------------- 12345 | 1 12349 | 3 12446 | 3 

and etc.

From this, I want to return only those records from the following MySQL query that have RequestID in the above table:

 SELECT wo.WORKORDERID, COALESCE(ti.FIRST_NAME,'Not Assigned') AS 'Technician', COALESCE(cd.CATEGORYNAME, 'Not Assigned') AS Category, COALESCE(scd.NAME, 'Not Assigned') AS Subcategory, wof.UDF_CHAR1 "Office Location" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID 

ie In this example, I only want to drop the records 12345, 12349 and 12446. In the end, I need one table that has the query ID, score, and columns from the MySQL query. However, if I can get a "filtered" MySQL table, I can join these two descendants. I just don't want to return MySQL “unfiltered” as the table will be huge.

Answer 1

With the right OLEDB database drivers (I just did this with PGSQL, so I can't recommend it), you can create a Linked Server in MSSQL. Here is the passage and here is more .

You can then query it using OPENQUERY as follows in MSSQL:

 select * from openquery(LinkedServerDb,'select * from remotetable') 

and join:

 select * from openquery(LinkedServerDb,'select * from remotetable') remoteTable join localTable on remotetable.someid=localtable.otherid