Read 145 times | Created 2014-04-15 08:22:12 | Updated 2014-04-15 08:22:12 | | |

CREATE TABLE tabel_a(
  id_a INT NOT NULL,
  nama VARCHAR(50) not null,
  kuota INT NULL
) ENGINE=MyISAM;

INSERT INTO tabel_a
VALUES
(1,'jalan-jalan ke bali',3),
(2,'jalan-jalan ke bangka',2),
(3,'jalan-jalan ke pulau seribu',4);

CREATE TABLE tabel_b(
  id_b INT NOT NULL,
  nama VARCHAR(50) not null,
  id_a INT NOT NULL
) ENGINE=MyISAM;

INSERT INTO tabel_b
VALUES
(1,'Asep',1),
(2,'Agus',2),
(3,'Dani',2);

SELECT 
  a.nama AS rekreasi,
  IF(a.kuota=c.curcount+1,b.nama,'-') as Nama
FROM tabel_a a
JOIN tabel_b b USING ( id_a )
JOIN 
(
  SELECT x.curcount
  FROM 
  (
        SELECT (A.A) AS curcount
        FROM (
          SELECT 0 AS A UNION ALL 
          SELECT 1 UNION ALL 
          SELECT 2 UNION ALL 
          SELECT 3 UNION ALL 
          SELECT 4 UNION ALL 
          SELECT 5 UNION ALL 
          SELECT 6 UNION ALL 
          SELECT 7 UNION ALL 
          SELECT 8 UNION ALL 
          SELECT 9) AS A
    ) x 
)c ON a.kuota>c.curcount
ORDER BY b.nama,c.curcount DESC;