0

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.

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"
| improve this answer | |
  • 1
    They probably need 2 joins (one for origin and another for destination). – ypercubeᵀᴹ May 16 '17 at 16:20
  • @ypercubeᵀᴹ probably, but the relation wasn't clear to me (they might need only one or the other in some cases?) – Erik Darling May 16 '17 at 16:21
1

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.