CODEWITHSUNDEEP
sql
na_krul
na_krul

Reputation: 21

How to select unique entries

I have a table of network devices interfaces (link_id points to id):

id      |device     |interface      |link_id
1       |switch1    |Gi1/1          |2
2       |switch2    |Gi1/1          |1
...

My goal is to have following connectivity matrix:

device_a    |interface_a    |device_b   |interface_b
switch1     |Gi1/1          |switch2    |Gi1/1

Which I thought I can get with:

SELECT a.device, a.interface, b.device, b.interface
FROM `links` a
INNER JOIN `links` b ON a.id = b.link_id

But as a result I get a "reverse" record as well:

device_a    |interface_a    |device_b   |interface_b
switch1     |Gi1/1          |switch2    |Gi1/1
switch2     |Gi1/1          |switch1    |Gi1/1

How can I select only unique entries? Is there any smarter way to achieve this task? (different table structure may be)

Upvotes: 0

Views: 55

Answers (2)

eshirvana
eshirvana

Reputation: 24633

add a condition like this:

SELECT a.device, a.interface, b.device, b.interface
FROM `links` a
INNER JOIN `links` b 
   ON a.id = b.link_id
   AND a.id < a.link_id

Upvotes: 2

WOUNDEDStevenJones
WOUNDEDStevenJones

Reputation: 5315

If you move your link_id column to a new table to relate id and link_id (renamed id_a and id_b below), the query for this becomes simple with 2 JOINs. This table structure also normalizes your data, because now the relationship between 2 interfaces is stored in a separate many-to-many table, not as additional IDs in the interface table.

CREATE TABLE interfaces (
  `id` INTEGER,
  `device` VARCHAR(7),
  `interface` VARCHAR(5)
);

INSERT INTO interfaces
  (`id`, `device`, `interface`)
VALUES
  ('1', 'switch1', 'Gi1/1'),
  ('2', 'switch2', 'Gi1/1');
  
CREATE TABLE links (
  `id_a` INTEGER,
  `id_b` INTEGER
);

INSERT INTO links
    (`id_a`, `id_b`)
VALUES
    ('1', '2');

SELECT
    i.device as device_a,
    i.interface interface_a,
    i2.device as device_b,
    i2.interface as inteface_b
FROM
    links l
    JOIN interfaces i ON i.id = l.id_a
    JOIN interfaces i2 ON i2.id = l.id_b;
device_a interface_a device_b inteface_b
switch1 Gi1/1 switch2 Gi1/1

View on DB Fiddle

Upvotes: 2

Related Questions