COUNT function and INNER JOIN on multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

COUNT function and INNER JOIN on multiple tables

Author

Topic

julyzerg
Starting Member

4 Posts

Posted - 2014-06-23 : 17:04:24

This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.

What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:

3 tables will be involved are:

Sample data for manager_id = 11

I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)

SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date
    FROM ((project_manager 
    INNER JOIN files 
    ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending')
    INNER JOIN projects
    ON projects.project_id = project_manager.project_id)
    GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date
    ORDER BY projects.status, projects.start_date DESC

result of this query:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-23 : 17:17:49

Could you show us some sample data for all 3 tables where manager_id = 11 (the linked data at least)?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

julyzerg
Starting Member

4 Posts

Posted - 2014-06-23 : 17:31:52

quote:
Originally posted by tkizer

Could you show us some sample data for all 3 tables where manager_id = 11 (the linked data at least)?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Yes I added info to first post.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-23 : 17:41:18

Try changing "SELECT COUNT(file_id) as 'Pending files'" to "SELECT COUNT(*) as 'Pending files'"

julyzerg
Starting Member

4 Posts

Posted - 2014-06-23 : 17:55:36

quote:
Originally posted by bitsmed

Try changing "SELECT COUNT(file_id) as 'Pending files'" to "SELECT COUNT(*) as 'Pending files'"

same result

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-23 : 19:30:47

Your sample data doesn't appear to match your expected output. Please correct.

Also, I don't understand your table design. I think you should remove the manager_id column from files. Isn't the relationship that files are part of a project and a project manager is assigned to a project? I don't understand why the manager info would be duplicated in the files table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources