ksno ksno - 11 months ago 44
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 Source

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