mardi 24 février 2015

mysql join rows as looping through child parent rows in the same table


I am trying to join child/parent rows from the same table in one query.


Table SQL:



CREATE TABLE `LocationMatrix` (
`IndexID` int(11) NOT NULL AUTO_INCREMENT,
`LocName` varchar(50) NOT NULL,
`_Parent` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`IndexID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOCK TABLES `LocationMatrix` WRITE;

INSERT INTO `LocationMatrix` (`IndexID`, `LocName`, `_Parent`)
VALUES
(1,'Root',0),
(16766,'Top Level Don\'t go any further',1),
(50330,'Rep5',16766),
(50385,'25',50330),
(50387,'A',50385),
(50388,'1',50387);

UNLOCK TABLES;


I want to be able to specify IndexID and get all child rows associated with it up until location indexid of 16766.


So if I specify IndexID as 50388, it will return rows with ids 50388, 50387, 50385, 50330 and when it sees a parent of 50330, which is 16766 - it should stop.


Logic: Take a row with specified IndexID, select it, then get that row's _Parent (which is an id of another row in the same table) select that row and repeat this until selecting a row with _Parent of 16766. I hope this makes sense.





Aucun commentaire:

Enregistrer un commentaire