We help IT Professionals succeed at work.

Sql to join multiple tables with just a count of rows from one of the tables

Diane Lonergan
Diane Lonergan used Ask the Experts™
on
I have a sql query (see below) which joins a project table to 3 other tables, the two left joins are fine and are returning what I need, the join on the document table will return a row for every document associated with the project, but I don't need the document details at all, I just need to return the count of how many documents are on the project, so effectively just want a single row, I don't know how to formulate this query, I know I need a subquery with a count, but the syntax defeats me, I attached an xls with the rows it has returned and beneath that I have added  a row showing what I would like to achieve.

select p.project_code, p.name
, s.name as 'Project Status'
, r.Name as 'Division Name'
from project p
join Document_Entities de on de.Entity_Identifier = p.Project_ID
left join Project_Status s on p.Project_Status_ID = s.Project_Status_ID
left join region r on p.Region_ID = r.Region_ID
where p.Project_Code = 1036
query.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Didn't look at the attachment nor set up a test case but try this:
select p.project_code, p.name
, s.name as 'Project Status'
, r.Name as 'Division Name'
, de.doc_count
from project p
join (select Entity_Identifier, count(*) doc_count from Document_Entities group by Entity_Identifier) de on de.Entity_Identifier = p.Project_ID
left join Project_Status s on p.Project_Status_ID = s.Project_Status_ID
left join region r on p.Region_ID = r.Region_ID
where p.Project_Code = 1036

Open in new window

Diane LonerganIT Operations Mgr

Author

Commented:
Great thank you, just the job