CODEWITHSUNDEEP
mysqlsql
valiD
valiD

Reputation: 359

Count rows from one tables of users in another table

I want to create a query for project listings that would give the number of registered applications, excluding the ones for which the user does not exist. In this case, considering user 10 does not exist, I should have the query results as folows:

RESULTS

 +----+------------+--------------+
    | id |  project   | applications |
    +----+------------+--------------+
    |  1 | MyProject1 |            3 |
    |  2 | MyProject2 |            0 |
    |  3 | MyProject3 |            0 |
    +----+------------+--------------+

TABLES

   Projects
+----+------------+
| id |    name    |
+----+------------+
|  1 | MyProject1 |
|  2 | MyProject2 |
|  3 | MyProject3 |
+----+------------+


applications
+----+------+------------+
| id | user | project_id |
+----+------+------------+
|  1 |    3 |          1 |
|  2 |    4 |          1 |
|  3 |    5 |          1 |
|  4 |   10 |          1 |
+----+------+------------+



users
+----+---------+
| id |  Name   |
+----+---------+
|  1 | Smith   |
|  2 | John    |
|  3 | Paul    |
|  4 | Chris   |
|  5 | Gabriel |
+----+---------+

The below query is not excluding the non-existing users:

SELECT  `projects` . * , (

                                                                                SELECT COUNT( * ) 
                                                                                FROM  `applications` 
                                                                                WHERE  `applications`.`project_id` =  `projects`.`id` 
                                                                                AND EXISTS (
                                                                                SELECT  `applications`.`id` 
                                                                                FROM  `applications` ,  `users`,`project`
                                                                                WHERE  `application`.`user` =  `users`.`id` AND `applications`.`project_id` = `project`.`id`
                                                                                )
                                                                                )  AS `applications`
                                                                                FROM  `projects` ORDER BY `id` DESC LIMIT 30

Upvotes: 0

Views: 57

Answers (3)

Guilhem Hoffmann
Guilhem Hoffmann

Reputation: 962

Based on previous solution

select p.id, p.name, count(u.id)
from projects p left join
     applications a
     on p.id = a.project_id left join
     users u
     on a.user = u.id
where u.id is not null
group by p.id, p.name;

When you do a left join, if the search value doesn't exists, it returns null. Then filtering by excluding null users, will give you the result.

Please find a sqlfiddle to illustrate it : http://www.sqlfiddle.com/#!9/cbfec6/3

But easiest solution would be 

select p.id, p.name, count(u.id)
from projects p,applications a, users u
where a.user = u.id
 and p.id = a.project_id 
group by p.id, p.name;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Your query looks overly complicated. This should do:

select 
  id, 
  name as project, 
  (
    select count(*) 
    from applications a
    where a.project_id = p.id
    and a.user in (select id from users)
  ) as applications
from projects p;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you want left join and group by:

select p.id, p.name, count(u.id)
from projects p left join
     applications a
     on p.id = a.project_id left join
     users u
     on a.user_id = u.id
group by p.id, p.name;

However, you might want to think about fixing the data. It seems like there should be foreign key relationships between applications and projects and applications and users. The ability to have an invalid user means that there is no valid foreign key relationship to users.

Upvotes: 1

Related Questions