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

Question

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?

Thanks for fast comment, I just added `GROUP BY FID_author` to the end of my each subquery, but it still returns 0 rows. — ksno, Sep 09 '16 at 09:37

ScaisEdge · Accepted Answer · 2016-09-09T10:43:44.457

4

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

edited Sep 09 '16 at 10:43

answered Sep 09 '16 at 09:37

ScaisEdge

131,976
10
91
107

It gave me right direction to make my query work. However, I had to `GROUP BY u.ID` the main query also. Now I just need to read more about `GROUP BY` and all possible `JOIN`'s and how they works. Thanks for fast answer. – ksno Sep 09 '16 at 09:44
@ksno .. in main query i don't see aggregation function so you don't should need group by .. eventually you need distinct for retrieve distinct value .. – ScaisEdge Sep 09 '16 at 09:48

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

1 Answers1