How to specify the parent query field from within a subquery in MySQL?
How do I specify the parent query field from within a subquery in MySQL?
For Example:
I have written a basic Bulletin Board type program in PHP.In the database each post contains: id(PK) and parent_id(the id of the parent post). If the post is itself a parent, then its parent_id is set to 0.
I am trying to write a mySQL query that will find every parent post and the number of children that the parent has.
$query = "SELECT id, (
SELECT COUNT(1)
FROM post_table
WHERE p开发者_StackOverflow中文版arent_id = id
) as num_children
FROM post_table
WHERE parent_id = 0";
The tricky part is that the first id doesn't know that it should be referring to the second id that is outside of the subquery. I know that I can do SELECT id AS id_tmp and then refer to it inside the subquery, but then if I want to also return the id and keep "id" as the column name, then I'd have to do a query that returns me 2 columns with the same data (which seems messy to me)
$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";
The messy way works fine, but I feel an opportunity to learn something here so I thought I'd post the question.
How about:
$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";
or if you put an alias on the p1.id, you might say:
$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";
You could try something like this
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
To get back to your example, use the alias of the main table in the sub select
SELECT pt.id,
(SELECT COUNT(1) FROM post_table WHERE parent_id = pt.id)
FROM post_table pt
WHERE pt.parent_id = 0
Give the tables unique names:
$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";
The following syntax works in Oracle. Can you test if the same works in MYSQL too? It is called scalar subquery in Oracle.
You would just need to alias the two tables differently to distinguish between them if you are using the same table twice.
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
Thanks Don. I had a nested query as shown below and a WHERE
clause in it wasn't able to determine alias v1
. Here is the code which isn't working:
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 */
);
So, I just added the alias v1
again inside the JOIN
. Which made it work.
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 */
);
Hope this will be helpful for someone.
Parent query field within a subquery in MySQL 8.
I'm selecing games scores on the basis of username from tblgamescores using nested query.
SELECT
GameScoresID,
(SELECT Username FROM tblaccounts WHERE AccountID = FromAccountID) AS FromUsername,
(SELECT Username FROM tblaccounts WHERE AccountID = ToAccountID) AS ToUsername,
(SELECT Username FROM tblaccounts WHERE AccountID = WinAccountID) AS WinUsername,
(SELECT Username FROM tblaccounts WHERE AccountID = LossAccountID) AS LossUsername,
FromUserScore,
ToUserScore
FROM tblgamescores a
WHERE FromAccountID = (SELECT AccountID FROM tblaccounts WHERE Username = "MHamzaRajput");
精彩评论