SQL LEFT-JOIN on 2 fields for MySQL

mysql sql join left-join
Possa picture Possa · Sep 20, 2013 · Viewed 106.7k times · Source

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

juergen d picture juergen d · Sep 20, 2013 
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

Related questions

MySQL select rows where left join is null

I have these MySQL tables: table1: id | writer 1 | Bob 2 | Marley 3 | Michael table2: user_one | user_two 1 | 2 And this query: SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id = table2.user_one This query will return all rows of …

Read More
How to use aliases with MySQL LEFT JOIN

My original query is doing joins using the WHERE clause rather than JOIN. I realized that this was not returning movies that did not have any stars or genres did not show up so I think I have to do …

Read More
MySQL left outer join with where clause - return unmatched rows

I have two tables: pq and pe. I am trying to LEFT OUTER JOIN left table (pq) on right table (pe). pq has primary key column id pe has two-column primary key, so it may have many pqid's or none …

Read More