ksno ksno - 3 months ago 6
MySQL Question

Why my query shows no errors, but also returns zero rows?

I have written this query to get as many rows as there are users + count of potentials that each user have created + all potentials that have been converted. This is how it looks like:

SELECT u.*, p.allPotentials, pc.cPotentials
FROM os_user u
JOIN (SELECT FID_author, count(*) allPotentials FROM os_potential) p
ON p.FID_author = u.ID
JOIN (SELECT converted, FID_author, count(*) cPotentials FROM os_potential) pc
ON p.FID_author = u.ID AND pc.converted = 1


I am trying to do it with uncorrelated subquery as this answer explained me, that I can combine my queries into 1. But im getting 0 rows.

My tables looks like this:

Users:

+----+------+-------+
| ID | Name | Email |
+----+------+-------+


Potentials:

+----+------+-------+------------+-----------+
| ID | Name | Email | FID_author | converted |
+----+------+-------+------------+-----------+


FID_author is foreign key, the user id.

My query is returning 0 rows and shows no errors. What am I doing wrong?

EDIT

So far my query:

SELECT u.*, p.allPotentials, pc.cPotentials
FROM os_user u
LEFT JOIN (SELECT FID_author, count(*) allPotentials
FROM os_potential GROUP BY FID_author) p
ON p.FID_author = u.ID
LEFT JOIN (SELECT converted, FID_author, count(*) cPotentials
FROM os_potential GROUP BY FID_author) pc
ON p.FID_author = u.ID
AND pc.converted = 1
GROUP BY u.ID


I am getting results almost as expected, but the problem is,
cPotentials
contains 1 in every row, which is false. There are much many then only 1. Where could be the problem?

Answer

Missing group by on subquery and eventully use left join

SELECT u.*, p.allPotentials, pc.cPotentials 
 FROM os_user u
LEFT JOIN (SELECT FID_author, count(*) allPotentials FROM os_potential 
                  GROUP BY FID_author) p 
 ON p.FID_author = u.ID 
LEFT JOIN (SELECT converted, FID_author, count(*) cPotentials FROM os_potential 
                   GROUP BY converted,FID_author) pc 
 ON pc.FID_author = u.ID AND pc.converted = 1