SQL LEFT-JOIN on 2 fields for MySQL

Question

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?

what did you tried? Show us example of your code – veljasije Sep 20 '13 at 10:40 — veljasije, Sep 20 '13 at 10:40
What your `WHERE` clause contains? – veljasije Sep 20 '13 at 10:42 — veljasije, Sep 20 '13 at 10:42

juergen d · Accepted Answer · 2013-09-20T11:02:18.573

83

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

edited Sep 20 '13 at 11:02

answered Sep 20 '13 at 10:40

juergen d

201,996
37
293
362

Thanks! It was the double = in the ON clause that I was missing! – Possa Sep 20 '13 at 10:49

score 6 · Answer 2 · answered Sep 20 '13 at 10:48

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

Thanks for you answer and the detail on the WHERE clause. – Possa Sep 20 '13 at 10:54 — Possa, Sep 20 '13 at 10:54

SQL LEFT-JOIN on 2 fields for MySQL

View A:

View B:

OUTPUT

2 Answers2

Linked