Mysql在同一列上连接多个列

Basically what I'm trying to accomplish, is I want the test.zfirewallaudit.src_ip AND test.zfirewallaudit.dst_ip fields to join on the inventory.dns_zones.ip column, so i can basically have an accurate hostname (inventory.dns_zones.subdomain) for test.zfirewallaudit.src_ip AND test.zfirewallaudit.dst_ip. so basically I want a column for src_ip subdomain and a column for dst_ip subdomain.

基本上我想要完成的是，我希望test.zfirewallaudit.src_ip和test.zfirewallaudit.dst_ip字段加入inventory.dns_zones.ip列，所以我基本上可以拥有一个准确的主机名（inventory.dns_zones.subdomain） ）for test.zfirewallaudit.src_ip AND test.zfirewallaudit.dst_ip。所以基本上我想要一个src_ip子域的列和一个dst_ip子域的列。

SELECT test.zfirewallaudit.src_ip, 
test.zfirewallaudit.src_zone, 
test.zfirewallaudit.dst_ip, 
test.zfirewallaudit.dst_zone, 
test.zfirewallaudit.dst_port, 
test.zfirewallaudit.rulename, 
test.zfirewallaudit.application,
inventory.dns_zones.subdomain
FROM test.zfirewallaudit LEFT JOIN inventory.dns_zones ON zfirewallaudit.src_ip = inventory.dns_zones.ip OR zfirewallaudit.dst_ip = inventory.dns_zones.ip LIMIT 10;

Problem is, I need the inventory.dns_zones.subdomain for both the test.zfirewallaudit.src_ip and ...dst_ip values. The current query only gives me 1 subdomain column

问题是，我需要inventory.dns_zones.subdomain来测试test.zfirewallaudit.src_ip和... dst_ip。当前查询只给出了1个子域列

1 个解决方案

#1

You need to join on inventory.dns_zones twice, once for the src and once for the dst:

您需要两次加入inventory.dns_zones，一次用于src，一次用于dst：

SELECT test.zfirewallaudit.src_ip, 
test.zfirewallaudit.src_zone, 
test.zfirewallaudit.dst_ip, 
test.zfirewallaudit.dst_zone, 
test.zfirewallaudit.dst_port, 
test.zfirewallaudit.rulename, 
test.zfirewallaudit.application,
dns_zones_src.subdomain,
dns_zones_dst.subdomain
FROM test.zfirewallaudit
LEFT JOIN inventory.dns_zones AS dns_zones_src ON ( zfirewallaudit.src_ip = dns_zones_src.ip )
LEFT JOIN inventory.dns_zones AS dns_zones_dst ON ( zfirewallaudit.dst_ip = dns_zones_dst.ip )
LIMIT 10;