php - 根据通配符 JOINS 过滤 MYSQL 结果

Question

我正在尝试过滤一个连接到 3 个不同表的表，其中一个连接将通配符作为连接条件的一部分。这导致结果输出额外/不需要的行。我不知道如何摆脱这些。

该设备由带有 1 或 2 个监控卡的机箱组成。每个管理卡都有自己的 IP 地址。如果机箱仅安装了一张监控卡，则其引用为:01-xxxx:N:xxxx <-N 在此位置

如果它有 2 张主管卡，则引用为:

01-xxxx:P:xxxx <-P 表示主要

01-xxxx:S:xxxx <-S 表示辅助

连接表由源机箱和目标机箱组成。这仅描述了机箱之间的连接。每个机箱仅由 01-xxxx:N:xxxx 或主交换机 Controller 01-xxxx:P:xxxx 描述。每个源和目的地都有两条“路径”:东 (E) 和西 (W)。

我正在尝试获取 01-xxxx:P:xxxx 的 IP 地址和01-xxxx:S:xxxx 来自“设备”表，其中“连接”表仅引用主设备 01-xxxx:P:xxxx，然后根据“设备”表中的 IP 地址是否在 IP 范围内从第三个表获取 VLAN ID，并使用以下命令正确显示每一行:East Source和Destination + East IP和EAST VLAN(基于范围内的IP)
和WEST 源和目标 + WEST IP 和 WEST VLAN(基于某个范围内的 IP)(如果存在)
设备表示例:

   `equipment` (`id`, `site_code`, `site_id`, `system_name`, `ip_add`, `mcast`, `sys_loc`, `systype`, `itamname`, `dimetis`, `DNS`) VALUES
(686, '25A2', 'TAS:BAUS:MTWEL', '01-25A2:P:TAS:BAUS:MT_WELINGTON', 172525962, 4013364224, 'ACLE RD,  PRK', 6, '01-25a2-p-tas-baus-mt-welington', 0, 1),
(687, '25A2', 'TAS:BAUS:MTWEL', '01-25A2:S:TAS:BAUS:MT_WELINGTON', 172526090, 4013364224, 'ACLE RD,  PRK', 6, '01-25a2-s-tas-baus-mt-welington', 0, 1);

连接表:

`connections` (`id`, `system_name_source`, `port_type`, `slot_source`, `port_source`, `system_name_dest`, `slot_dest`, `port_dest`, `cable`, `side_name`, `side`, `status`) VALUES
(332, '01-54A1:P:TAS:TLS:BATHURST', '1G Tunk', '10', '01', '01-25A2:P:TAS:BAUS:MT_WELINGTON', '12', '01', 'D 25A2 54A1 DD001', '01-54A2:P:TAS:TLS:DAVEY', 'W', 1),
(343, '01-54A2:P:TAS:TLS:DAVEY', '1G Tunk', '10', '01', '01-25A2:P:TAS:BAUS:MT_WELINGTON', '11', '01', 'D 25A2 54A2 DD001', '01-54A1:P:TAS:TLS:BATHURST', 'E', 1);

VLAN/IP 范围表:

    `vlan_agg` (`id`, `vl_system_name`, `vlan_id`, `ip_sub`, `bcast`, `cidr`) VALUES
(40, '01-54A2:P:TAS:TLS:DAVEY', 72, 172525952, 172526079, 25),
(41, '01-54A1:P:TAS:TLS:BATHURST', 73, 172526080, 172526207, 25);

我的查询:

  SELECT 

connections.system_name_dest,
connections.side,
tgt.system_name AS system_name,
tgt.ip_add AS dest_ip,
dvlan.vlan_id AS d_vlan_id

FROM connections 
LEFT JOIN equipment tgt on SUBSTRING(tgt.system_name,1,8) like SUBSTRING(connections.system_name_dest,1,8)
LEFT JOIN vlan_agg dvlan on tgt.ip_add BETWEEN dvlan.ip_sub AND dvlan.bcast

where system_name_dest='01-25A2:P:TAS:BAUS:MT_WELINGTON'
Order By side

这会产生四行，其中 2 行的目标 IP 地址和 VLAN 不正确:

Array ( [system_name_dest] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [side] => E [system_name] => 01-25A2:S:TAS:BAUS:MT_WELINGTON [dest_ip] => 172526090 [d_vlan_id] => 73 ) 
Array ( [system_name_dest] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [side] => E [system_name] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [dest_ip] => 172525962 [d_vlan_id] => 72 ) 
Array ( [system_name_dest] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [side] => W [system_name] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [dest_ip] => 172525962 [d_vlan_id] => 72 ) 
Array ( [system_name_dest] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [side] => W [system_name] => 01-25A2:S:TAS:BAUS:MT_WELINGTON [dest_ip] => 172526090 [d_vlan_id] => 73 )

正确的输出应该是在设备表中具有基于主卡的东 IP 地址和东 VLAN 以及基于辅助卡的西 IP 和西 VLAN:

Array ( [system_name_dest] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [side] => E [system_name] => 01-25A2:S:TAS:BAUS:MT_WELINGTON [dest_ip] => 172526090 [d_vlan_id] => 73 )
Array ( [system_name_dest] => 01-25A2:P:TAS:BAUS:MT_WELINGTON [side] => W [system_name] => 01-25A2:S:TAS:BAUS:MT_WELINGTON [dest_ip] => 172526090 [d_vlan_id] => 73 )

如有任何帮助，我们将不胜感激。我已经为此苦苦挣扎了好几天。

Answer 1

我添加的额外字段提供了足够的信息来开发有效的过滤器。(感谢@solarflare - 他的问题引发了解决方案)

查询现在看起来像:

    SELECT 

connections.system_name_dest,
connections.side,
tgt.system_name AS system_name,
tgt.ip_add AS dest_ip,
dvlan.vlan_id AS d_vlan_id

FROM connections 
LEFT JOIN equipment tgt on SUBSTRING(tgt.system_name,1,8) like SUBSTRING(connections.system_name_dest,1,8)
LEFT JOIN vlan_agg dvlan on tgt.ip_add BETWEEN dvlan.ip_sub AND dvlan.bcast

where system_name_dest='01-25A2:P:TAS:BAUS:MT_WELINGTON' and ((system_name NOT LIKE '%:S:%' and side = 'E') OR (system_name LIKE '%:S:%' and side = 'W') )
Order By side