Save, people. I have a table in MySQL where each record has the member's CPF and the indicator's CPF (who indicated it).
I'm trying to select both the indicator and its indicators. For example: I indicated A, A indicated B and B indicated C. I want from A to be able to map all of the following.
Until then I have the following query, which only brings the direct indication:
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;
Inserting test data:
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 query result would be to return these three data, since one indicated the other, from the first record. In this case, I only need these 2 depth levels, the main one, who he indicated and the one indicated by the indicated one.
I appreciate any help and if you need more information just comment.
EDITED SQL Fiddle: link