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?
2 Answers
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
Let's try this way:
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 /*if you has to filter by columns from right table , then add this condition in ON clause*/
where a.somecolumn = somevalue /*if you have to filter by some column from left table, then add it to where condition*/
So, in where clause you can filter result set by column from right table only on this way:
...
where b.somecolumn <> (=) null
