Access/SQL Server 2008加入“赞"无法正常工作 [英] Access/SQL Server 2008 Join using Like not working

问题描述

我提前道歉，因为该问题在该站点上以多种形式出现过多次，但是我尝试实施的所有解决方案似乎都无法正常工作，并且我找不到找到正确答案的已回答问题.

I apologize ahead of time because this question is in various forms many times on this site, but none of the solutions I have tried implementing seem to work properly, and I cannot find an answered question that gets me the right answer.

我正在为一位同事进行访问，尽管将来一旦他可以设置服务器就可以使用SQL.我正在尝试使用Like连接两个表，这很容易，除了它不能正常工作.首先，来自每个表的示例:

I am working in access for a co-worker, though it will probably be in SQL in the future once he can get a server set up. I am trying to join two tables using Like, which is easy enough, except that it just is not working properly. First, an example from each table:

T1

+-------------------------+--------------------------------------------------------------+--------------+
|LOCATION_CG_LOC_LEGACY_ID|LOCATION_CODE_DESC                                            |PI_LOW_SIDE_MW|
+-------------------------+--------------------------------------------------------------+--------------+
|55555-Opt01              |Autobot Transformer, 800/900 NETWORK, Optimus #1 - 800 NETWORK|5             |
+-------------------------+--------------------------------------------------------------+--------------+
|55555-Opt02              |Autobot Transformer, 800/900 NETWORK, Optimus #2 - 800 NETWORK|6             |
+-------------------------+--------------------------------------------------------------+--------------+
|55555-Opt03              |Autobot Transformer, 800/900 NETWORK, Optimus #3 - 800 NETWORK|6.5           |
+-------------------------+--------------------------------------------------------------+--------------+

T2

+------+-----------------------------------------+------------+
|DIVID |DMT Bank Name                            |5 Digit Code|
+------+-----------------------------------------+------------+
|647531|800/900 NETWORK, Optimus #1 - 800 NETWORK|55555       |
+------+-----------------------------------------+------------+
|647532|800/900 NETWORK, Optimus #2 - 800 NETWORK|55555       |
+------+-----------------------------------------+------------+
|647533|800/900 NETWORK, Optimus #3 - 800 NETWORK|55555       |
+------+-----------------------------------------+------------+

我试图从本质上合并类似的项目，以便我可以执行所需的任何查询.但是现在，我可以选择全部.我只需要合并类似的项目.在T1中，第一列[LOCATION_CG_LOC_LEGACY_ID]是非重复的唯一项.在T2中，[DIVID]是非重复的唯一项.我试图使用

I am trying to combine like items essentially, so that I can perform whatever queries I want. For now however, I am fine with a select all. I just need to combine like items. In T1, the first column [LOCATION_CG_LOC_LEGACY_ID] is a non-repeating unique item. In T2, [DIVID] is a non-repeating unique item. I was trying to query them together using

SELECT *
FROM [Data Table 1] INNER JOIN [Data Table 2] ON (([t1].[LOCATION_CODE_DESC] Like [t2].[DMT Bank Name]
OR [t2].[DMT Bank Name] Like [t1].[LOCATION_CODE_DESC]) AND ([T1].[LOCATION_CG_LOC_LEGACY_ID] Like [t2].[5 Digit Code] or [t2].[5 Digit Code] Like [T1].[LOCATION_CG_LOC_LEGACY_ID]));

现在，我认为该连接条件的后半部分存在问题，但是即使仅使用前半部分，我也会得到零结果.但是，如果我编辑每个表的中间列，然后使用该条件的前半部分，则效果很好.换句话说，如果我使列完全匹配，则查询似乎可以正常工作，这无视使用"like"联接开头的全部要点.

Now, I think there is a problem with the second half of that join condition, but even using only the first half, I get ZERO results. However, if I edit the middle column in each table, then use the first half of that condition, it works perfectly. In other words, if I make the columns match exactly, the query seems to work, which defies the whole point in using a "like" join to begin with.

我还没有在SQL Server中尝试过此方法，但是至少无论如何，我都需要使它在Access中工作，至少可以作为概念证明.各位女士和先生们可以给我的任何帮助将不胜感激，即使这将我引向了我错过的另一个话题时，也是如此.

I have not yet tried this in SQL Server, but I need to get it working in Access anyway as a proof of concept at least. Any help you wonderful ladies and gentlemen can give me would be appreciated, even if it is pointing me to another thread that I have missed like a dolt.

最后，如果可能的话，我试图避免使用VBA.

Lastly, I am trying to avoid using VBA if possible.

推荐答案

在sqlserver中使用LIKE时，您需要添加通配符，例如%，否则本质上成为对是否相等(=)的检查.这是您使用通配符的查询:

You need to add wildcards such as % when using LIKE in sqlserver, otherwise it essentially becomes a check for equality (=). here is your query with wildcards:

SELECT *
FROM  t1 
INNER JOIN  t2 
ON [t1].[LOCATION_CODE_DESC]   Like '%' + [t2].[DMT Bank Name]+ '%' 
AND [T1].[LOCATION_CG_LOC_LEGACY_ID] Like '%' + CONVERT(varchar(50),[t2].[5 Digit Code] )+ '%';

这是一个小提琴: http://sqlfiddle.com/#!3/dc2b9 /10/0

为了兼容，您还需要将int转换为某种varchar.

You'll also need to convert to int to a varchar of some sort for compatibility.

不过请注意，对类似对象执行join，尤其是进行转换时，将导致大型数据集的性能非常差.

Note though, that doing a join on a like, especially with a convert will result in very poor performance for large data sets.

修改:MS Access

当您将int转换为字符串并使用不同的通配符时，MS Access将会有所不同.我没有安装访问权限，因此无法测试，但是我认为您的MS Access查询如下所示:

MS Access will be different when you convert int to string, and uses different wildcards. I don't have access installed so can't test, but I think your MS Access query would look like this:

SELECT *
FROM  t1 
INNER JOIN  t2 
ON [t1].[LOCATION_CODE_DESC]   Like '*' & [t2].[DMT Bank Name] & '*' 
AND [T1].[LOCATION_CG_LOC_LEGACY_ID] Like '*' & CStr( [t2].[5 Digit Code] ) & '*';

这篇关于Access/SQL Server 2008加入“赞"无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！