downline

-- memakai database TEST
use TEST;

-- buat tabel baru
DROP TABLE IF EXISTS tblmember;
CREATE TABLE IF NOT EXISTS tblmember (
no_id CHAR(5) PRIMARY KEY,
nama CHAR(1),
alamat VARCHAR(10),
tlp VARCHAR(10),
upline CHAR(1)
);

-- masukkan contoh data ke dalam tabel
INSERT INTO tblmember
VALUES
('11202','B','Jakarta','081225030','A'),
('11201','A','Medan','081225020',''),
('11203','C','Bandung','081225051','A'),
('11204','D','Bogor','081225052','B');

-- tampilkan seluruh data
SELECT * FROM  tblmember;
+-------+------+---------+-----------+--------+
| no_id | nama | alamat  | tlp       | upline |
+-------+------+---------+-----------+--------+
| 11201 | A    | Medan   | 081225020 |        |
| 11202 | B    | Jakarta | 081225030 | A      |
| 11203 | C    | Bandung | 081225051 | A      |
| 11204 | D    | Bogor   | 081225052 | B      |
+-------+------+---------+-----------+--------+
4 rows in set (0.00 sec)

-- tampilkan nama yg punya downline
SELECT
  upline AS nama,
  COUNT(upline) AS downline
FROM
  tblmember
WHERE
  upline <> ''
GROUP BY
  upline;

+------+----------+
| nama | downline |
+------+----------+
| A    |        2 |
| B    |        1 |
+------+----------+
2 rows in set (0.00 sec)

-- tampilkan nama yg punya 2 downline
SELECT
  upline AS nama,
  COUNT(upline) AS downline
FROM
  tblmember
GROUP BY
  upline
HAVING
  downline=2;
+------+----------+
| nama | downline |
+------+----------+
| A    |        2 |
+------+----------+
1 row in set (0.00 sec)

-- tampilkan data yg punya downline kurang dari 2
SELECT
  a.*
FROM
  tblmember a
LEFT JOIN(
  SELECT
    upline,
    COUNT(upline) AS downline
  FROM
    tblmember
  GROUP BY
    upline
  HAVING
    downline=2
) b
ON
  b.upline=a.nama
WHERE
  b.upline IS NULL;

+-------+------+---------+-----------+--------+
| no_id | nama | alamat  | tlp       | upline |
+-------+------+---------+-----------+--------+
| 11202 | B    | Jakarta | 081225030 | A      |
| 11203 | C    | Bandung | 081225051 | A      |
| 11204 | D    | Bogor   | 081225052 | B      |
+-------+------+---------+-----------+--------+
3 rows in set (0.00 sec)