MySQL function needed

Assume the following table in mysql:

DROP TABLE `test`;

CREATE TABLE `test` (

`item_no` varchar(3) DEFAULT NULL,

`requires` varchar(3) DEFAULT NULL,

`relationship_id` int(11) NOT NULL,

PRIMARY KEY (`relationship_id`)

) ENGINE=InnoDB;

INSERT INTO `test` VALUES ('B','A',1),('D','B',2),('D','A',3),('E','D',4),('E','B',5),('F','E',6),('E','G',7),('G','C',8);

it represents a list of the relationships between different items:

item 'B' requires item 'A'

item 'D' requires item 'B'

item 'D' requires item 'A'

item 'E' requires item 'D'

item 'F' requires item 'E'

item 'E' requires item 'G'

item 'G' requires item 'C'

etc.

I would like to have an optimized fast query as a function, which when it receives an item no, returns back all the items it potentially can also have a required relationship with, and do not already have a relationship defined.

these are the possible results:

function('A') returns 'C'

function('B') returns 'C'

function('C') returns 'A','B','D'

function('D') returns 'C','G'

function('E') returns nothing

function('F') returns nothing

function('G') returns 'A','B','D'

MySQL PostgreSQL SQL

ID Proyek: #4329080

Tentang proyek