php - subconsultas - tipos de consultas en mysql

¿Cómo especificar el campo de consulta principal desde una subconsulta en mySQL?

Question

¿Hay alguna forma de especificar el campo de consulta principal desde una subconsulta en mySQL?

Por ejemplo:
Escribí un programa básico de tipo tablón de anuncios en PHP.

En la base de datos cada publicación contiene: id (PK) y parent_id (la identificación de la publicación principal). Si la publicación es en sí misma un elemento primario, entonces parent_id se establece en 0.

Intento escribir una consulta mySQL que encuentre todas las publicaciones principales y la cantidad de hijos que tenga el padre.

$query = "SELECT id, (
      SELECT COUNT(1) 
      FROM post_table 
      WHERE parent_id = id
) as num_children
FROM post_table
WHERE parent_id = 0";

La parte difícil es que la primera identificación no sabe que debería estar refiriéndose a la segunda identificación que está fuera de la subconsulta. Sé que puedo hacer SELECCIONAR ID AS id_tmp y luego referirme a él dentro de la subconsulta, pero luego si también quiero devolver la identificación y mantener "id" como el nombre de la columna, entonces tendría que hacer una consulta que me devuelva Me 2 columnas con los mismos datos (que me parece desordenado)

$query = "SELECT id, id AS id_tmp, 
            (SELECT COUNT(1)
            FROM post_table
            WHERE parent_id = id_tmp) as num_children
         FROM post_table
         WHERE parent_id = 0";

La manera desordenada funciona bien, pero siento la oportunidad de aprender algo aquí, así que pensé en publicar la pregunta.

Answer 1

Dale a las tablas nombres únicos:

$query = "SELECT a.id, (SELECT COUNT(1) FROM post_table b WHERE parent_id = a.id) as num_children FROM post_table a WHERE a.parent_id = 0";

Answer 2

Gracias Don. Tenía una consulta anidada como se muestra a continuación y una cláusula WHERE en ella no fue capaz de determinar el alias v1 . Aquí está el código que no está funcionando:

Select 
    teamid,
    teamname
FROM
    team as t1
INNER JOIN (
    SELECT 
        venue_id, 
        venue_scores, 
        venue_name 
    FROM venue 
    WHERE venue_scores = (
        SELECT 
            MAX(venue_scores) 
        FROM venue as v2 
        WHERE v2.venue_id = v1.venue_id      /* this where clause wasn''t working */
    ) as v1    /* v1 alias already present here */
);

Entonces, acabo de agregar el alias v1 nuevamente dentro de JOIN . Lo cual lo hizo funcionar.

Select 
    teamid,
    teamname
FROM
    team as t1
INNER JOIN (
    SELECT 
        venue_id, 
        venue_scores, 
        venue_name 
    FROM venue as v1              /* added alias v1 here again */
    WHERE venue_scores = (
        SELECT 
            MAX(venue_scores) 
        FROM venue as v2 
        WHERE v2.venue_id = v1.venue_id   /* Now this works!! */
    ) as v1     /* v1 alias already present here */
);

Espero que esto sea útil para alguien.

Answer 3

La siguiente sintaxis funciona en Oracle. ¿Puedes probar si lo mismo funciona en MYSQL también? Se llama subconsulta escalar en Oracle.

Solo necesitaría aliar las dos tablas de manera diferente para distinguirlas si usa la misma tabla dos veces.

sql> select empno,
  2         (select dname from dept where deptno = emp.deptno) dname
  3    from emp 
  4    where empno = 7369;

     EMPNO DNAME
---------- --------------
      7369 RESEARCH

sql> select parent.empno,
  2         (select mgr from emp where empno = parent.empno) mgr
  3    from emp parent
  4    where empno = 7876;

     EMPNO        MGR
---------- ----------
      7876       7788

Answer 4

Podrías probar algo como esto

SELECT  pt.id,
        CountTable.Cnt
FROM    post_table pt LEFT JOIN
        (
            SELECT  parent_id,
                    COUNT(1) Cnt
            FROM    post_table
            WHERE   parent_id <> 0
            GROUP BY parent_id
        ) CountTable ON pt.id = CountTable.parent_id
WHERE   pt.parent_id = 0

Para volver a su ejemplo, use el alias de la tabla principal en la selección secundaria

SELECT  pt.id,
        (SELECT COUNT(1) FROM post_table WHERE parent_id = pt.id) 
FROM    post_table pt
WHERE   pt.parent_id = 0

Answer 5

Qué tal si:

$query = "SELECT p1.id, 
                 (SELECT COUNT(1) 
                    FROM post_table p2 
                   WHERE p2.parent_id = p1.id) as num_children
            FROM post_table p1
           WHERE p1.parent_id = 0";

o si pones un alias en el p1.id, puedes decir:

$query = "SELECT p1.id as p1_id, 
                 (SELECT COUNT(1) 
                    FROM post_table p2 
                   WHERE p2.parent_id = p1.id) as num_children
            FROM post_table p1
           WHERE p1.parent_id = 0";