Mysql left join multiple columns on same column

Question

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.

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

kmoser · Accepted Answer · 2017-08-30 21:19:28Z

1

You need to join on inventory.dns_zones twice, once for the src and once for the 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;

answered Aug 30, 2017 at 21:19

kmoser

9,3693 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql left join multiple columns on same column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related