使用“LIKE”连接多个表数据 [英] Joining Multiple Tables with "LIKE" Data

问题描述

请原谅我，如果这已得到回答，但我无法提出正确的问题。

情景：


我有3个表，我想创建一个报表，所以首先我开始创建结果集的查询。

表：

展开 | 选择 | Wrap | 行号

解决方案

字段的命名有点令人困惑，尤其是tblC的控制标识符这一事实表A中的ControlNumber链接。

据我所知，y你需要从tblA和tblB之间的外连接开始，给出CN和CEN列（可选地填充）。

接下来你需要将tblC与tblA和/或tblB链接起来。我可能会在外连接查询中添加一个字段，其中包含tblA.Controlnumber和tblB.controlenhancementnumber（可选）的串联。

据我所知，这两个值（当两者都存在时）将相等。


现在你可以通过tblC ControlIdentifier和tblC ControlIdentifier的串联来创建一个用于tblC的UNION和基于连接的外连接，以匹配完全填充的第一列和第二个查询对于单个的第一个UNION查询中未匹配的CCIN代码值连接字段。

只需确保第二个查询包含&;用于CEN列的虚拟。

最后使用虚拟CCIN列添加外连接查询以获得没有CCIN编号的组合。


我伪代码：

1）首先在tblA和tblB上加入a.controlnumber" dominant"

和填充字段cn，cen和a和b控制数字的串联/>
2）UNION 3折：

a-从查询1中选择[cn]，[cen]，ccin，其中tblC控制标识符&控制标识符与串联字段匹配。

给出xxx行

UNION

b-从查询1中选择[cn]，[cen]，ccin其中tblC single [控制标识符]与串联字段匹配。排除先前（a-）查询中已经存在的tblC CCIN

给出xx行

UNION

c select [cn]，[cen ]，来自查询1的ccin，其中没有找到tblC CCIN


Nic; o）

我只是快速阅读了你的想法但是我走出了门。我上传了一些示例数据，以帮助澄清我正在尝试做的事情。


快速解释有关表C的更多信息 - 控制标识符：


此值与控制号（表A）和控件相关联增强数字（表B）。 CCI是一个增强控制数的数据集，但也增强了控制增强数。我希望这有助于清理它。


我会在今晚晚些时候看看你的解决方案。非常感谢你！


-SB

好的，所以我看到你在说什么，但数据设置不是那么完整。


每种外观的价值如下：


tblA。[控制号码] {A-1，A-2，A-3 ，...}

tblB。[控制增强编号] {A-2（1），A-2（2），A-3（1），A-5（1）,. ..}

tblC。[CCI标识符] {A-1，A-2，A-2（2），A-3（1），A-4，...}


所以你可以看到你可以连接这三个中的任何一个。你可以连接tblA。[控制号码]& tblB。[控制号码]。


所以到目前为止我已经这样做了：

展开 | 选择 | Wrap | 行号

Forgive me if this has been answered, but I am struggling with an inability to ask the right question.

Scenario:

I have 3 Tables, which I would like to create one Report with, so first I am beginning with a query to create the result set.

Tables:

Expand|Select|Wrap|Line Numbers

解决方案

The naming of the fields is a bit confusing, especially the fact that Control Identifier of tblC links to ControlNumber in Table A.
As far as I see, you would need to start with an outer join between tblA and tblB, giving the CN and CEN column (optionally filled).
Next you would need to link the tblC with tblA and/or tblB. I would probably add to the outer join query a field with the concatenation of tblA.Controlnumber and tblB.controlenhancementnumber (optional).
As far as I understand these two values (when both exist) will be equal.

So now you can create a UNION for tblC and the outer join based on a join by the concatenation of tblC ControlIdentifier and tblC ControlIdentifier to match the fully filled first columns and the second query for CCIN codes not matched in the first UNION query for the "single" value concatenation field.
Just make sure that this second query holds a "" dummy for the CEN column.
Finally add the outer join query with a dummy CCIN column to get the combinations not having a CCIN number.

I pseudo code:
1) First left join on tblA and tblB with a.controlnumber "dominant"
and filling field cn, cen and a concatenation of a and b controlnumber
2) UNION 3 fold:
a- select [cn], [cen], ccin from query 1 where tblC Control Identifier & Control Identifier matches the concatenation field.
gives x x x rows
UNION
b- select [cn], [cen], ccin from query 1 where tblC single [Control Identifier] matches the concatenation field. Exclude the tblC CCIN''s already in previous (a-) query
gives x x rows
UNION
c select [cn], [cen], ccin from query 1 where no tblC CCIN has been found

Nic;o)

I just got a quick read of your thoughts but I am headed out the door. I uploaded some sample data to help clarify what I am attempting to do.

To quickly explain more about the Table C - Control Identifier:

This value is linked to both Control Numbers (Table A) and Control Enhancement Numbers (Table B). The CCI is a data set that enhances Control Numbers but also enhances Control Enhancement Numbers. I hope this helps clear it up.

I will look at your solution later this evening. Thank you very much!!

-SB

Ok so I see what you are saying but the data is not quite set up that way.

The values for each look as such:

tblA.[Control Number] {A-1,A-2,A-3,...}
tblB.[Control Enhancement Number] {A-2(1),A-2(2),A-3(1),A-5(1),...}
tblC.[CCI Identifier] {A-1,A-2,A-2(2),A-3(1),A-4,...}

So as you can see you can concatenation any of the three. You could concatenation tblA.[Control Number] & tblB.[Control Number].

So I have done this so far:

Expand|Select|Wrap|Line Numbers

这篇关于使用“LIKE”连接多个表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！