We help IT Professionals succeed at work.

Help with joining reults of two queries

Diane Lonergan
Diane Lonergan used Ask the Experts™
on
I have a variation on a similar question I posted last week. How can I join these two queries together to produce the output as shown in the attached spreadsheet

select p.Project_Code --THIS CODE CORRECTLY RETURNS PROJECT DETAILS
, p.Name as 'Project Name'
, s.name as 'Project Status'
, r.Name as 'Division Name'
from Project p
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 in (1563)

--I NEED TO JOIN ABOVE RESULT WITH BELOW RESULT

select   --count(*) DocCnt  THIS CODE CORRECTLY GETS THE NUMBER OF DOCUMENTS
p.Project_Code, p.name as 'Project Name'/*, s.name as 'Project Status' */
--, de.entity_identifier
--, p.project_id
--,title
--, d.Document_ID
--, coalesce(convert(varchar(12),d.published_date,3),'') 'Pub Date'
--, coalesce(convert(varchar(12),d.modified_date,3),'') 'Mod Date'

from document d
join Document_Entities de on d.Document_ID = de.Document_ID
join entity_class EC on de.Entity_Class_ID = ec.Entity_Class_ID and ec.Entity_Class_ID = 3
join Project p on p.Project_ID = de.Entity_Identifier
join Project_Status s on p.Project_Status_ID = s.Project_Status_ID
where p.project_code in (1563)
Book1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
look at using Common Table Expressions (CTE)

https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15



; with CTE as (

select    -- THIS CODE CORRECTLY GETS THE NUMBER OF DOCUMENTS
p.Project_Code, p.name as 'Project Name'/*, s.name as 'Project Status' */
,  count(*) as DocCnt
--, de.entity_identifier
--, p.project_id
--,title
--, d.Document_ID
--, coalesce(convert(varchar(12),d.published_date,3),'') 'Pub Date'
--, coalesce(convert(varchar(12),d.modified_date,3),'') 'Mod Date'

from document d
join Document_Entities de on d.Document_ID = de.Document_ID
join entity_class EC on de.Entity_Class_ID = ec.Entity_Class_ID and ec.Entity_Class_ID = 3
join Project p on p.Project_ID = de.Entity_Identifier
join Project_Status s on p.Project_Status_ID = s.Project_Status_ID
where p.project_code in (1563))  /* remove the where clause if you want this table to include all projects, projectname;s and their document counts */
Group by p.project_code,p.pname




select p.Project_Code --THIS CODE CORRECTLY RETURNS PROJECT DETAILS
, p.Name as 'Project Name'
, s.name as 'Project Status'
, r.Name as 'Division Name',CTE.DocCnt as "No Docs"
from Project p
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
join CTE on CTE.Project_Code=p.project_code
where p.project_code in (1563)

Open in new window


You can see the columns in CTE by running
use select * from CTE to see the Column names.


without seeing your data merely combining your query.
you could let CTE include all list of such that the last where 1563 can be changed and you get a response without needing to make changes to all.

i.e. CTE will have all project_code, project_name, doccounts

with the second query getting the one of interest to you or a set if you use where p.project_code in (1563,1525,1278)

you will get 3 lines as the output.  all it takes i
Diane LonerganIT Operations Mgr

Author

Commented:
Arnold,

Thank you for your response, but due to my inexperience I don't understand your solution and can't get it to work, but I appreciate your time.
Distinguished Expert 2017
Commented:
Cte creates a table named cte in the example that includes the results of a query.

Your second query then incorporates the data from this memory based table to complete the info you need.

Please explain what issue are you having with the first part, cte definition?
Diane LonerganIT Operations Mgr

Author

Commented:
Hi Arnold

I get this error on the Group BY clause. I have copied your text exactly

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'Group'.

cheers
Diane LonerganIT Operations Mgr

Author

Commented:
Hi Arnold

I have just spotted the error and now it works fine. The double parentheses after the where should have been a single, then the removed parentheses should have been after the group by,

Thnks for all your help.
Distinguished Expert 2017

Commented:
Right, initially I had your first query in the CTE
When you posted the excel it changed the reasoning/logic as the info for cte is the document counting query......
...
...
When using cte there is a consideration which query shoukd be within.

You can have two memory based cte cte1 and then use a third query to combine the results of the other two.

There might be another way to achieve the same result using group by project_code, project Name, .....such that the count..

In a single query. For that info/schema table

Left join/right join ........
Without that, using cte was the straightforward.. Grab info from one as a new table, join it in the other query.

Group by defines allowed elements in the query, all others have to be aggregated, sum(), count(), etc.