Mysql – inner join shows more entries

join;MySQL

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.

Best Answer

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"
Related Question