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";