CODEWITHSUNDEEP
mysqlsql
opticMan
opticMan

Reputation: 19

MySQL join 2 tables A,B by id, where A.id is a substring of B.id

I have 2 tables, 'Node' and 'Equipment'

The Node Table has node id, name, and function. Each node is full of equipment, given in the Equipment table. The equipment id contains the node id + equipment designation text. So the node id is a substring of the equipment id. I want to be able to query what equipment a node has by using the node id in the equipment table.

How can I structure a query to:

"Join the tables given the node id, Add the node longName column entry to each piece of equipment in the equipment table containing the node id (within its equipment id)"

Edit: This is not a duplicate question as it relies on the use of "LIKE", which was not present in the other linked answer

Node table
+--------------------------------------+------------+----------------+
| id                                   | longName   | memberFunction |
+--------------------------------------+------------+----------------+
| 15c6a1ef-ff1c-3486-bce9-8157906a2995 | North_Node | DGE            |
| 182019b4-b2e7-307b-85c6-2a39001a54ff | South_Node | DGE            |
| 1adf96c7-8e0d-35d9-9d00-1c0acf6c6f92 | East_Node  | ROADM          |
| 1d14064f-6bd9-3d11-862f-abc25612d88f | West_Node  | ILA            |
+--------------------------------------+------------+----------------+

Equipment table
+----------------------------------------------------+-------+------+
| id                                                 | shelf | slot |
+----------------------------------------------------+-------+------+
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21      |    21 | NULL |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1    |    21 |    1 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10   |    21 |   10 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10_2 |    21 |   10 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_11   |    21 |   11 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_13   |    21 |   13 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14   |    21 |   14 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14_2 |    21 |   14 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_15   |    21 |   15 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_16   |    21 |   16 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_1 |    21 |   17 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_2 |    21 |   17 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_3 |    21 |   17 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1_2  |    21 |    1 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_2    |    21 |    2 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_3    |    21 |    3 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_5    |    21 |    5 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_6    |    21 |    6 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_7    |    21 |    7 |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_9    |    21 |    9 |
........................


Desired output table
+----------------------------------------------------+-------+------+------------+
| id                                                 | shelf | slot | longName   |
+----------------------------------------------------+-------+------+------------+
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21      |    21 | NULL | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1    |    21 |    1 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10   |    21 |   10 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10_2 |    21 |   10 | North_Node | 
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_11   |    21 |   11 | North_Node | 
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_13   |    21 |   13 | North_Node | 
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14   |    21 |   14 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14_2 |    21 |   14 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_15   |    21 |   15 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_16   |    21 |   16 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_1 |    21 |   17 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_2 |    21 |   17 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_3 |    21 |   17 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1_2  |    21 |    1 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_2    |    21 |    2 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_3    |    21 |    3 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_5    |    21 |    5 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_6    |    21 |    6 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_7    |    21 |    7 | North_Node |
| 15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_9    |    21 |    9 | North_Node |
.............................................

Upvotes: 0

Views: 59

Answers (3)

nbk
nbk

Reputation: 49375

A simple join is enough

CREATE TABLE Node (
  `id` VARCHAR(36),
  `longName` VARCHAR(10),
  `memberFunction` VARCHAR(5)
);

INSERT INTO Node
  (`id`, `longName`, `memberFunction`)
VALUES
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995', 'North_Node', 'DGE'),
  ('182019b4-b2e7-307b-85c6-2a39001a54ff', 'South_Node', 'DGE'),
  ('1adf96c7-8e0d-35d9-9d00-1c0acf6c6f92', 'East_Node', 'ROADM'),
  ('1d14064f-6bd9-3d11-862f-abc25612d88f', 'West_Node', 'ILA');

CREATE TABLE Equipmen (
  `id` VARCHAR(50),
  `shelf` INTEGER,
  `slot` VARCHAR(4)
);

INSERT INTO Equipmen
  (`id`, `shelf`, `slot`)
VALUES
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21', '21', 'NULL'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1', '21', '1'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10', '21', '10'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10_2', '21', '10'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_11', '21', '11'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_13', '21', '13'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14', '21', '14'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14_2', '21', '14'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_15', '21', '15'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_16', '21', '16'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_1', '21', '17'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_2', '21', '17'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_3', '21', '17'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1_2', '21', '1'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_2', '21', '2'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_3', '21', '3'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_5', '21', '5'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_6', '21', '6'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_7', '21', '7'),
  ('15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_9', '21', '9');

SELECT e.*,n.`longName` 
FROM Equipmen e INNER JOIN Node n ON n.id = LEFT(e.id,36)

id                                                 | shelf | slot | longName  
:------------------------------------------------- | ----: | :--- | :---------
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21      |    21 | NULL | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1    |    21 | 1    | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10   |    21 | 10   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_10_2 |    21 | 10   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_11   |    21 | 11   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_13   |    21 | 13   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14   |    21 | 14   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_14_2 |    21 | 14   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_15   |    21 | 15   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_16   |    21 | 16   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_1 |    21 | 17   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_2 |    21 | 17   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_17_3 |    21 | 17   | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_1_2  |    21 | 1    | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_2    |    21 | 2    | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_3    |    21 | 3    | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_5    |    21 | 5    | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_6    |    21 | 6    | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_7    |    21 | 7    | North_Node
15c6a1ef-ff1c-3486-bce9-8157906a2995::EQPT_21_9    |    21 | 9    | North_Node

db<>fiddle here

Upvotes: 1

The Impaler
The Impaler

Reputation: 48810

You can do:

select
  e.*,
  n.longName
from Equipment e
join Node n on e.id like concat(n.id, '%')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269943

One method is:

select n.*, e.*
from nodes n join
     equipment e
     on n.id = substring_instr(e.id, ':', 1);

A second method is

select n.*, e.*
from nodes n join
     equipment e
     on e.id = concat(n.id, '::%');

Upvotes: 1

Related Questions