Joining Two Queries - SQL Server Forums

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

Joining Two Queries

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-17 : 17:16:46

Hi

I have 2 queries , which i need to join to get the expected output ,

Here is the first query and sample output i get :

select B.RESOURCE_NAME from dbo.DW_T_ASW_HOST_CODE B

left outer join dbo.DW_T_SUBCLUSTER_RESOURCE C on C.RESOURCE=B.RESOURCE_NAME

where B.RESOURCE_NAME <> 'Not Defined' and C.RESOURCE is null

Sample Output:

RESOURCE_NAME
qctbld04_rwalters

QCTBLD04_yuanz

Test1-CRM2

TEST2-CRM1_DBjobs

Here is my 2nd Query :

SELECT DISTINCT A.RESOURCE,A.SUB_CLUSTER_NAME,COUNT(*)as Jobs,ROW_NUMBER()over (partition by count(*) order by A.sub_cluster_name)as row_num
FROM DW_T_ASW_HOST_JOBDATA A
LEFT OUTER JOIN DW_T_SUBCLUSTER_RESOURCE C ON A.RESOURCE=C.RESOURCE
WHERE C.RESOURCE IS NULL AND A.RESOURCE <> 'Not Defined'
GROUP BY A.RESOURCE,A.SUB_CLUSTER_NAME

Sample Output:
RESOURCE SUB_CLUSTER_NAM Jobs Row_num
qctbld04_rwalters HCI 20 1
QCTBLD04_yuanz HCI 113 1

Now , i need to combine both the above queries to get the expected output as below , Please Help...

RESOURCE SUB_CLUSTER_NAME Jobs row_num
qctbld04_rwalters HCI 20 1
QCTBLD04_yuanz HCI 113 1
Test1-CRM2 NULL NULL NULL
TEST2-CRM1_DBjobs NULL NULL NULL

SD_Monkey
Starting Member

38 Posts

Posted - 2010-06-17 : 22:14:43

[code]
Select a.RESOURCE_NAME,b.SUB_CLUSTER_NAME,b.Jobs,b.row_num
(select B.RESOURCE_NAME from dbo.DW_T_ASW_HOST_CODE B
left outer join dbo.DW_T_SUBCLUSTER_RESOURCE C on C.RESOURCE=B.RESOURCE_NAME
where B.RESOURCE_NAME <> 'Not Defined' and C.RESOURCE is null) a
left join
(SELECT DISTINCT A.RESOURCE,A.SUB_CLUSTER_NAME,COUNT(*)as Jobs,ROW_NUMBER()over (partition by count(*) order by A.sub_cluster_name)as row_num
FROM DW_T_ASW_HOST_JOBDATA A
LEFT OUTER JOIN DW_T_SUBCLUSTER_RESOURCE C ON A.RESOURCE=C.RESOURCE
WHERE C.RESOURCE IS NULL AND A.RESOURCE <> 'Not Defined'
GROUP BY A.RESOURCE,A.SUB_CLUSTER_NAME) b
on a.RESOURCE_NAME=b.RESOURCE

[/code]

hope this work

A maze make you much more better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources