Select category and subcategory within the same table

Question

I have the table called modulos Inside it I include idModule, idModuloBase, module, folder and dating creation

When the idModuloBase is 0, it is the category, if it is > 0, it is the completed module. I need to make an SQL that lists the categories and subcategories within each one...

Can anyone suggest me any SQL to do that? Thank you.

Answer 1

As there are only two levels, just one JOIN of the table with itself (a self John):

SELECT
   modulo.*, 
   principal.idModulo AS idModuloBase,
   principal.modulo AS moduloBase,
   principal.pasta AS pastaBase
FROM modulos AS modulo
INNER JOIN modulos AS principal
ON principal.idModulo = modulo.idModuloBase

Answer 2

I don’t see a query that can solve the problem well, so I suggest doing it in PHP.

<?php

  $sql = "SELECT * FROM modulos WHERE idMoludoBase = 0";
  // Execute sua query

  $modulos = Array();
  while ($row = $res->fetch()) {

     $sql = "SELECT * FROM modulos WHERE idMoludoBase = {$row['idModulo']}";
     // Execute sua query

     // Se ten submodulos
     if ($sres){
        // Adicione todos na chave submodulos
        $row['submodulos'] = $sres->fetchAll();
     }

     // Adicona a linha ao array de modulos
     $modulos[] = $row;
  }

_{The code acime is "only an algorithm", must be adapted according to its drive of database.}

Variable output $modulos:

  $modulos => Array(
     [
        idModulo      => 1
        idModuloBase  => 0 
        modulo        => Cadastros
        ...
        submodulos    => Array(
           [
              idModulo       => 2
              idModuloBase   => 1
              modulo         => Cedentes
              ...
           ],
           [ ... ]
        )
     ], 
     [ ... ]
  )