So I have a SQL query like this:
SELECT p.*
FROM parent p JOIN join_table j ON p.id=j.parent_id
WHERE j.child_id=1 OR j.child_id=2
GROUP BY j.parent_id
HAVING COUNT(j.child_id)=2;
Which I got from this excellent answer here. Now I would like to modify the query to return the count of all the parent elements there. unfortunately due to the GROUP BY statement I am unable to do this (as it will give the count of each grouped statement).
My attempt was the obvious and naive approach:
SELECT COUNT(DISTINCT(p.id))
FROM parent p JOIN join_table j ON p.id=j.parent_id
WHERE j.child_id=1 OR j.child_id=2
GROUP BY j.parent_id
HAVING COUNT(j.child_id)=2;
Thanks for helping.
Try this:
SELECT COUNT(*) FROM
(SELECT p.*
FROM parent p JOIN join_table j ON p.id=j.parent_id
WHERE j.child_id=1 OR j.child_id=2
GROUP BY j.parent_id
HAVING COUNT(j.child_id)=2) AS res;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments