0
All right, y'all. I have a table in Mysql in which each record has the member’s CPF and the indicator’s CPF (who indicated it).
I’m trying to select both the nominee and your indicators. For example: I indicated A, A indicated B and B indicated C. I want from A I can map all the following indicated.
Until then I have the following query, which only brings the indicated direct:
SELECT u.*, s.statusdesc AS ststatus, p.plandesc, p.planval,
(SELECT COUNT(*) FROM usuarios WHERE uscpfindicador = u.uscpf) AS indicados
FROM usuarios u, planos p, usstatus s
LEFT JOIN usuarios AS child
ON child.usid = usid
WHERE u.usstatus = s.statusid
AND u.usplano = p.planid
AND u.uscpfindicador = 0123456
GROUP BY u.usid;
The table:
CREATE TABLE IF NOT EXISTS `usuarios` (
`usid` int(11) NOT NULL AUTO_INCREMENT,
`usnome` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`usnascimento` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
`uscpf` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
`ustelefone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`uscelular` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`uscpfindicador` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
`usvencimento` int(11) NOT NULL,
`usendereco` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
`usbanco` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
`usagencia` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`usconta` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
`usoperacao` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`usfavorecido` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`usemail` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
`ussenha` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`usvendedor` int(11) NOT NULL DEFAULT '1' COMMENT 'boolean',
`usstatus` int(11) NOT NULL DEFAULT '1',
`usplano` int(11) NOT NULL DEFAULT '1',
`usdepende` int(11) DEFAULT NULL COMMENT 'se o usuário for dependente de alguém, o id do usuário do qual ele depende',
`ustipocad` int(11) DEFAULT NULL,
`usparentesco` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`usadmin` int(11) DEFAULT '0',
`recover` int(11) DEFAULT '0',
PRIMARY KEY (`usid`),
UNIQUE KEY `uscpf` (`uscpf`),
KEY `usplano` (`usplano`),
KEY `usstatus` (`usstatus`),
KEY `FkTipoCad` (`ustipocad`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Data entry test:
INSERT INTO `usuarios` (`usid`, `usnome`, `usnascimento`, `uscpf`, `ustelefone`, `uscelular`, `uscpfindicador`, `usvencimento`, `usendereco`, `usbanco`, `usagencia`, `usconta`, `usoperacao`, `usfavorecido`, `usemail`, `ussenha`, `usvendedor`, `usstatus`, `usplano`, `usdepende`, `ustipocad`, `usparentesco`, `usadmin`, `recover`) VALUES
(1, 'EMPRESA', '1988-05-29', '0123456', NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, '[email protected]', '7bca1fa5a8bcc4df3933e77c644b8cd1', 1, 1, 1, NULL, 3, NULL, 1, 0),
(2, 'Indicado pela empresa', '1992-03-22', '12345678910', '210000000', '21900000000', '0123456', 10, NULL, NULL, NULL, NULL, NULL, 'Jhonatan Pereira', '[email protected]', '709aae0fea527912f8a02cf1d7c20819', 1, 1, 1, NULL, 3, NULL, 0, 0),
(6, 'Indicado pelo indicado', '1992-03-22', '12312312311', '210000000', '21900000000', '12345678910', 10, 'Av Teste', '', '', '', '', '', '[email protected]', '709aae0fea527912f8a02cf1d7c20819', 1, 1, 1, NULL, 3, NULL, 0, 0);
An example of the result of the query would return these three data, since one indicated the other, from the first record. In this case, I need only those 2 levels of depth, the main, whom he indicated and the indicated by the indicated.
I appreciate any help and if you need more information just comment.
EDITED SQL Fiddle: http://sqlfiddle.com/#! 9/7bae60/3