asked on

Union 2 queries to a cte (temp table perhaps)

I have 3 queries like follows: I am not sure how to make the union due to the cte...I was thinking the cte might have to be a temp table and the the unions some how combined that way..Please help with the syntax.

--need to union these two queries..

--clients not with linked contacts and in root site
select c.cName as [Client Name], c.cFEIN as [Client Id], '' as [name] from cadoc_crm..tclient c
where c.nid not in (select xc.nIDClient from cadoc_crm..tClientXContact xc)
and c.cSiteCode in( select csitecode from cadoc_system..tsite where cpsitecode = '')

--clients not with linked contacts from contacts not internal to the site.
select c.cName as [Client Name], c.cFEIN as [Client Id], '' as [name] from cadoc_crm..tclient c
where c.nid in (select xc.nIDClient from cadoc_crm..tClientXContact xc where xc.nIDContact not in (select c.nid from cadoc_crm..tContact c
inner join cadoc_system..tSubscrXCrmCont cx
on c.nid = cast(cx.cidCRMCont as int)
inner join cadoc_system..tSubscr s
on cx.nidSubscr = s.nid
where s.cSiteCode in (select cSiteCode from cadoc_system..tsite where cSiteCode = '')))

-----Then union them to a temp table derived from this:

WITH CTE1 AS (
select clnt.cname as [Client Name],clnt.cfein as [Client Id],c.cFirstName + ' ' + c.cLastName as [name] from

(Select x.* from cadoc_crm..tClient c
inner join cadoc_crm..tClientXContact x
on c.nid = x.nIDClient
where c.cSiteCode in( select csitecode from cadoc_system..tsite where cpsitecode = '')
)t1
inner join cadoc_crm..tclient clnt
on t1.nIDClient = clnt.nid
inner join cadoc_crm..tContact c
on c.nid = t1.nIDContact
where clnt.lActive = 1 and c.nidClient in(select distinct sx.nidclient
from cadoc_system..tsitexcrmclient sx
inner join (select nid,csitecode from cadoc_system..tsite where cpsitecode = '')s
on sx.nidSite = s.nid and sx.cSiteCode = s.cSiteCode))
select [Client Name], [Client Id],
stuff(( select ',' + name from cte1 c1
where c2.[Client Name] = c1.[Client Name]
and c2.[Client Id] = c1.[Client Id]
for xml path('')), 1, 1, '') Names
from cte1 c2
group by [Client Name], [Client Id]
order by [Client Name] asc

Mike Eghtebas

This is a sample query from my article. See "Table Expression Subquery>Non-database Object>CTE, Common Table Expression>Recursive " section (under number 4):

-- Problem statement: Write a query to return the management chain from EmploeeID = 7. 

;WITH BossCTE                                        -- Recursive CTE, example 4.3
AS             
(
SELECT EmployeeID
     , ReportsTo
     , firstname
     , lastname
     , 0 AS distance
FROM dbo.Employees 
WHERE EmployeeID=7
UNION ALL
SELECT M.EmployeeID
     , M.ReportsTo
     , M.firstname
     , M.lastname
     , S.distance + 1 AS distance
FROM BossCTE AS S JOIN dbo.Employees AS M
ON S.ReportsTo = M.EmployeeID
) 
SELECT EmployeeID
     , ReportsTo
     , firstname
     , lastname
     , distance
FROM BossCTE
Order By distance DESC;

Open in new window

As you read through it, I will try to apply it to your code.

Mike

Pawan Kumar

Hi Robb,
Please find the fixed code for you.

Please try ...

;WITH CTE AS 
( 
	select clnt.cname as [Client Name],clnt.cfein as [Client Id],c.cFirstName + ' ' + c.cLastName as [name] 
	from
	(
		Select x.* from cadoc_crm..tClient c 
		inner join cadoc_crm..tClientXContact x 
		on c.nid = x.nIDClient 
		where c.cSiteCode in( select csitecode from cadoc_system..tsite where cpsitecode = '') 
	)t1 
	inner join cadoc_crm..tclient clnt 
	on t1.nIDClient = clnt.nid 
	inner join cadoc_crm..tContact c 
	on c.nid = t1.nIDContact 
	where clnt.lActive = 1 and c.nidClient in(select distinct sx.nidclient 
	from cadoc_system..tsitexcrmclient sx 
	inner join (select nid,csitecode from cadoc_system..tsite where cpsitecode = '')s 
	on sx.nidSite = s.nid and sx.cSiteCode = s.cSiteCode)
)
,CTE1 AS
( 
	SELECT DISTINCT [Client Name], [Client Id], stuff(( select ',' + name from cte c1 
	where c2.[Client Name] = c1.[Client Name] 
	and c2.[Client Id] = c1.[Client Id] 
	for xml path('')), 1, 1, '') Name 
	from cte c2 	
)
,CTE2 AS
(
	SELECT DISTINCT * FROM
	(
		select c.cName as [Client Name], c.cFEIN as [Client Id], '' as [name] from cadoc_crm..tclient c 
		where c.nid not in (select xc.nIDClient from cadoc_crm..tClientXContact xc) 
		and c.cSiteCode in( select csitecode from cadoc_system..tsite where cpsitecode = '')
		UNION ALL
		select c.cName as [Client Name], c.cFEIN as [Client Id], '' as [name] from cadoc_crm..tclient c 
		where c.nid in 
		(select xc.nIDClient from cadoc_crm..tClientXContact xc where xc.nIDContact not in 
		(select c.nid from cadoc_crm..tContact c 
		inner join cadoc_system..tSubscrXCrmCont cx 
		on c.nid = cast(cx.cidCRMCont as int) 
		inner join cadoc_system..tSubscr s 
		on cx.nidSubscr = s.nid 
		where s.cSiteCode in (select cSiteCode from cadoc_system..tsite where cSiteCode = '')))
	)k
)
SELECT DISTINCT * FROM 
(
	SELECT * FROM CTE1 
	UNION ALL
	SELECT * FROM CTE2
)k1 ORDER BY k.[Client Name] ASC

Open in new window

Hope it helps!

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robb Hill

ASKER

I dont see how this will give the correct results.

Essentially I want query 1 + query 2 + the results of the CTE query. = total results

SOLUTION