Possa Possa - 7 days ago 5
MySQL Question

SQL LEFT-JOIN on 2 fields for MySQL

I have a view

A
and a view
B
.

In
A
I have a lot of information about some systems, like
IP
and
port
which I want to preserve all. In
B
I have just one information that I want to add at
A
.

The matching fields between the two views are
IP
and
Port
. So I have to match those hosts which has the same IP and Port in both views.

Examples:

View A:



IP | OS | Hostname | Port | Protocol
1 | Win | hostONE | 80 | tcp
1 | Win | hostONE | 443 | tcp
1 | Win | hostONE | 8080 | tcp
2 | Linux | hostTWO | 21 | tcp
2 | Linux | hostTWO | 80 | tcp
3 | Linux | hostTR | 22 | tcp


View B:



IP | Port | State
1 | 443 | Open
2 | 80 | Closed


OUTPUT



IP | OS | Hostname | Port | Protocol | State
1 | Win | hostONE | 80 | tcp |
1 | Win | hostONE | 443 | tcp | Open
1 | Win | hostONE | 8080 | tcp |
2 | Linux | hostTWO | 21 | tcp | Closed
2 | Linux | hostTWO | 80 | tcp |
3 | Linux | hostTR | 22 | tcp |


Note: Is possible that some hosts of the view A has no IP/Port related items in View B.

Is also possible that some hosts of the view A has some match in the View B.

I thought that I should be using LEFT JOIN in order to have all the entry of View A and the correct associated entry of View B, but it didn't work.
I'm not able to adjust the query with the right WHERE clause and JOIN solution.

Any idea?

Answer
select a.ip, a.os, a.hostname, a.port, a.protocol,
       b.state
from a
left join b on a.ip = b.ip 
           and a.port = b.port
Comments