Robb Hill
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..tSubscrXCrmC ont 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..tsitexcrmcli ent 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
--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
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
inner join cadoc_system..tSubscrXCrmC
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
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..tsitexcrmcli
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
Hi Robb,
Please find the fixed code for you.
Please try ...
Hope it helps!
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
Hope it helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Essentially I want query 1 + query 2 + the results of the CTE query. = total results
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am still testing. I will be back on this soon with results..thanks again
Hi Robb, Is this done :) ?
ASKER
Sorry Pawan. I will close it tomorrow.
ASKER
Thank you as always Pawan. Sorry it took so long for me to test.
Open in new window
As you read through it, I will try to apply it to your code.Mike