Menu
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:
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 IP | Port | State 1 | 443 | Open 2 | 80 | Closed 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?
898
Left Join: SyntaxSELECT * FROM table1 LEFT [ OUTER ] JOIN table2 ON table1. column_name=table2. column_name; SQL LEFT join fetches a complete set of records from table1, with the matching records (depending on the availability) in table2.
If you'd like to get data stored in tables joined by a compound key that's a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns. In one joined table (in our example, enrollment ), we have a primary key built from two columns ( student_id and course_code ).
Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.
The following example shows how to concatenate three different columns: (SELECT id, email1 AS email FROM customer) UNION (SELECT id, email2 AS email FROM customer) UNION (SELECT id, email3 AS email FROM customer) ORDER BY id, email; As you can see, it's important that all the queries return the same columns.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
