inner join shows more entries

Question

maybe this is a really basic question, but I'm having problems whit it

I want to generate a new table based on info taken from those two tables:

Trelaciones:

+-------------+-------------+------+-----+-------------------+-------+
| Field       | Type        | Null | Key | Default           | Extra |
+-------------+-------------+------+-----+-------------------+-------+
| Device_SN_O | varchar(25) | NO   | PRI | NULL              |       |
| Device_SN_D | varchar(25) | NO   | PRI | NULL              |       |
| Interface   | varchar(25) | YES  |     | NULL              |       |
| Port_ID     | varchar(25) | YES  |     | NULL              |       |
| date        | timestamp   | NO   |     | CURRENT_TIMESTAMP |       |
+-------------+-------------+------+-----+-------------------+-------+

dispositivos:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sn        | varchar(25) | NO   | PRI | NULL    |       |
| device_id | varchar(25) | YES  |     | NULL    |       |
| ip_adress | varchar(25) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

dispositivos has a record of all devices in a network, its primary key is sn and this field match with Device_SN_O (O for origin) and Device_SN_D (D for destiny)

Trelaciones has information about the connections

let's say that I want to search for all devices connected to certain device i do something like this:

SELECT * FROM Trelaciones WHERE Device_SN_O= "FCQ1527Y0B3";

Result:

+-------------+-------------+--------------------+-----------------------+---------------------+
| Device_SN_O | Device_SN_D | Interface          | Port_ID               | date                |
+-------------+-------------+--------------------+-----------------------+---------------------+
| FCQ1527Y0B3 | FOC1929S37N | GigabitEthernet0/1 | GigabitEthernet1/0/23 | 2017-04-27 09:58:11 |
+-------------+-------------+--------------------+-----------------------+---------------------+

I would like to generate an inner join sentence that creates a table like this:

+-----------------+-----------------+--------------------+-----------------------+---------------------+
| IP_origin       | IP_destiny      | Interface          | Port_ID               | date                |
+-----------------+-----------------+--------------------+-----------------------+---------------------+
| 148.228.110.249 | 148.228.110.250 | GigabitEthernet0/1 | GigabitEthernet1/0/23 | 2017-04-27 09:58:11 |
+-------------+-------------+--------------------+-----------------------+---------------------+

So far I've tried this:

SELECT dispositivos.ip_adress,Trelaciones.Interface, Trelaciones.Port_ID 
FROM Trelaciones 
INNER JOIN dispositivos 
ON Trelaciones.Device_SN_O = "FCQ1527Y0B3";

But it is not even close to the result i expect, this is returning 89 rows and I don't know why.

Answer 1

This part of your join is incorrect:

Trelaciones.Device_SN_O="FCQ1527Y0B3"

That should be your WHERE clause.

Your join should be:

FROM Trelaciones 
INNER JOIN dispositivos 
ON  Trelaciones.whatever_key_column = dispositivos.whatever_key_column

So

FROM Trelaciones 
INNER JOIN dispositivos 
ON  Trelaciones.whatever_key_column = dispositivos.whatever_key_column
WHERE Trelaciones.Device_SN_O="FCQ1527Y0B3"

Answer 2

Your current query effective says match every row of dispositivos to every row of Trelaciones where Device_SN_O = "FCQ1527Y0B3". That's because your ON clause doesn't match anything between the tables; it only filters one of them.

The ON part of your JOIN should match a key column from Trelaciones to a key column from dispositivos. You can filter down to Device_SN_O = "FCQ1527Y0B3" in the WHERE clause. As ypercubeᵀᴹ mentioned, you probably want to join Trelaciones to dispositivos twice (once for origin and once for destiny):

SELECT o.ip_address AS IP_origin
     , d.ip_address AS IP_destiny
     , t.Interface
     , t.Port_ID
     , t.date
FROM Trelaciones t
     INNER JOIN dispositivos o
       ON t.Device_SN_O = o.sn
     INNER JOIN dispositivos d
       ON t.Device_SN_D = d.sn;