Non so se sei ancora in cerca di risposte, ma pubblico una possibile soluzione per aiutare, magari, qualcun altro.
L'organizzazione dello schema come lo hai descritto è corretto:
- una tabella per la definizione degli utenti
- una tabella per le relazioni
Di seguito un esempio:
Tabella utenti
CREATE TABLE utenti (
userid VARCHAR(10) PRIMARY KEY
);
INSERT INTO utenti(userid) VALUES ('001');
INSERT INTO utenti(userid) VALUES ('002');
INSERT INTO utenti(userid) VALUES ('003');
INSERT INTO utenti(userid) VALUES ('004');
INSERT INTO utenti(userid) VALUES ('002-1');
INSERT INTO utenti(userid) VALUES ('002-2');
Tabella relazioni
Sto assumendo che un utente possa avere un solo padre, quindi sarà presente come figlio massimo una sola volta nella tabelle delle relazioni (per questo childid può essere usato come PRIMARY KEY)
CREATE TABLE utenti_rel (
childid VARCHAR(10) PRIMARY KEY,
parentid VARCHAR(10),
CONSTRAINT FK_parentid_userid FOREIGN KEY (parentid) REFERENCES utenti(userid),
CONSTRAINT FK_childid_userid FOREIGN KEY (childid) REFERENCES utenti(userid)
);
INSERT INTO utenti_rel(childid, parentid) VALUES ('002', '001');
INSERT INTO utenti_rel(childid, parentid) VALUES ('003', '001');
INSERT INTO utenti_rel(childid, parentid) VALUES ('004', '001');
INSERT INTO utenti_rel(childid, parentid) VALUES ('002-1', '002');
INSERT INTO utenti_rel(childid, parentid) VALUES ('002-2', '002');
La query che potresti utilizzare è:
SELECT
u.userid
, ur_c.parentid
, ur_p.childid
FROM utenti u
LEFT JOIN utenti_rel ur_p ON u.userid = ur_p.parentid
LEFT JOIN utenti_rel ur_c ON u.userid = ur_c.childid
Where u.userid = '%%'
dove sostituire ‘%%’ con l'id dell'utente che vuoi cercare. Alcuni esempi:
u.userid = ‘001’
userid parentid childid
001 [NULL] 002
001 [NULL] 003
001 [NULL] 004
u.userid = ‘002’
userid parentid childid
002 001 002-1
002 001 002-2
u.userid = ‘003’
userid parentid childid
003 001 [NULL]
u.userid = ‘002-1’
userid parentid childid
002-1 002 [NULL]