INNER JOIN实施 [英] INNER JOIN implementation

问题描述

问题：



我需要在2个SQL之间执行 INNER JOIN 查询，但该查询必须访问位于自己服务器上的2个MySQL数据库。



小例子可以更好地解释我面临的问题：

 选择 * 
 来自 Table1，  -    Table1位于服务器72.93.200.11  
  INNER   JOIN 表2   -   表2位于服务器109.93.1.219  
  on  Table1.Id = Table2.Id; 

相关信息：

• MFC和ODBC用于数据库访问
• 我没有使用过ODBC，也没有使用过ODBC在
• 应用程序之前用于数据库编程的MFC是一个合法的cy one，所以我不能使用C ++ 11或更新版
• 使用Visual Studio 2008

如果需要进一步的信息，请发表评论。



问题：



如果使用m_strFilter [ ^ ]第二个 CRecordset 是可能的，你能指导我怎么做（再次，我没有MFC和ODBC的经验）？



我也会接受C ++解决方案，但请记住，我可能不会使用C ++ 11或更新版本，因为该应用程序是遗留的。



我有什么试过：



这个SO帖子 [ ^ ]建议使用 FEDERATED ENGINE 。



我是根据帖子中的各种评论，我不愿意使用这种方法，因为它的性能很差。



其他选项是分别执行两个查询，并过滤记录集代码。



我已经成功执行了单独的查询，但是 m_strFilter 只接受固定字符串，而不是 CRecordset 。



在写这个问题时，我试图弄清楚如何绕过上述限制。

解决方案

在程序中创建2个连接;每个服务器一个。



查询一个;然后使用第一个结果查询另一个。



当然，这需要考虑加入所有。



SQL Server支持分布式查询。您可以从SQL Server通过ODBC访问MySQL。



链接服务器和分布式查询SQL不良做法 [ ^

PROBLEM:

I need to perform INNER JOIN between 2 SQL queries, but that query would have to access 2 MySQL databases located on their own servers.

Small example might explain better the problem I face:

select * 
from Table1,        -- Table1 is located on server 72.93.200.11
INNER JOIN Table2   -- Table2 is located on server 109.93.1.219
on Table1.Id = Table2.Id;

RELEVANT INFORMATION:

• MFC and ODBC are used for database access
• I haven't used ODBC, nor MFC for database programming before
• application is a legacy one, so I can not use C++ 11 or newer
• Visual Studio 2008 is used

If further information is required please leave a comment.

QUESTION:

If using m_strFilter[^] with the second CRecordset is possible, can you instruct me how to do it (again, I have no prior experience with MFC and ODBC)?

I will accept C++ solution as well, but remember that I may not use C++ 11 or newer, since the application is a legacy one.

What I have tried:

This SO post[^] suggest usage of FEDERATED ENGINE.

I am reluctant to use this approach since it has poor performance, according to various comments in the post.

Other option would be to perform both queries separately, and filter recordsets in code.

I have successfully executed separate queries, but m_strFilter accepts fixed string only, not CRecordset.

While writing this question, I am trying to figure out how to bypass the above limitation.

解决方案

You create 2 connections in your program; one to each server.

Query one; then query the other using the results of the first.

Of course, this requires thinking beyond "join all".

SQL Servers support "distributed queries". You can get to MySQL via ODBC from SQL Server.

Linked servers and distributed queries | SQL Bad Practices[^]

这篇关于INNER JOIN实施的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！