Associated Name spiderweb

Question

Say for instance I have the following entries in my table:

ID - 1
Name - Daryl
  
ID - 2 
Name - Terry

ID - 3
Name - Dave

ID - 4
Name - Mitch

I eventually wish to search my table(s) for one specific name, but show all associated names. For instance,

Searching Daryl will return Terry, Dave & Daryl.

Searching Terry will return Dave, Daryl & Terry

Searching Mitch will only return Mitch.

The current table housing the names is as followed:

--
-- Table structure for table `members`
--

CREATE TABLE `members` (
  `ID` int(255) NOT NULL,
  `GuildID` int(255) NOT NULL,
  `ToonName` varchar(255) NOT NULL,
  `AddedOn` date NOT NULL,
  `AddedByID` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`ID`, `GuildID`, `ToonName`, `AddedOn`, `AddedByID`) VALUES
(1, 1, 'Daryl', '2020-01-17', 5),
(2, 1, 'Terry', '2020-01-17', 5),
(3, 1, 'Mitch', '2020-01-17', 5),
(4, 1, 'Dave', '2020-01-17', 5);

--

For Reference. GuildID will be a default search criteria based on the searchers login details. With a spiderweb like this, how would I go about creating another table (or another Column) to bring a combined search spiderweb structure based on the search criteria?

I was thinking something along the lines of:

CREATE TABLE `Associated`(
    `ID` INT(255) NOT NULL,
    `MainID` INT(255) NOT NULL,
    `SecondaryID` INT(255) NOT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Associated` (`ID`, `MainID`, `SecondaryID`) VALUES

(1, 1, 2) -- Daryl Associated With Terry
(2, 1, 4) -- Daryl Associated With Dave

But I feel this will make an over complicated value structure with alot of redundant inputs. Is there a more effective way to create a unified search?

The whole idea of operation is that each name is Individual. So certain Entries can be put under Daryl, Terry acting alone. But one search will bring together all associated Names by searching one name then pull together total entries based on the alisas?

Answer 1

You can try This

Select IFNULL(m.ToonName , members.ToonName) as ToonName 
from members 
LEFT JOIN Associated on Associated.MainID = members.ID 
LEFT JOIN members as m on m.ID = Associated.SecondaryID 
Where members.ToonName = "Mitch"

While you have entry for "Mitch" in Associated table it will return you Daryl and when you dont have associated Id it will return the name from members table.

And If you will check this with "Daryl", it will give you Two results,

Select IFNULL(m.ToonName , members.ToonName) as ToonName 
from members 
LEFT JOIN Associated on Associated.MainID = members.ID 
LEFT JOIN members as m on m.ID = Associated.SecondaryID 
Where members.ToonName = "Daryl"

In case you want all the names in a single column you can use GROUP_CONCAT as @flash suggested in another answer.