how to combine the sql results from diff sql state

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

how to combine the sql results from diff sql state

Author

Topic

eirikr
Starting Member

18 Posts

Posted - 2008-10-31 : 14:35:37

with 3 diff tables:
v_Collection tbl: CollectionID Name
1 walgreen
2 target
3 sears
v_FullCollectionMembership: CollectionID ResourceID isClient
1 100 1
2 200 1
3 300 0
v_image: ResourceID imageVersion
100 123
200 null
300 null
First sql:
select T1.Name, T2.ResourceID, count(T2.ResourceID) as Cnt_s1
from v_Collection as T1, v_FullCollectionMembership as T2
where T1.CollectionID = T2.CollectionID
group by T1.Name,T2.ResourceID

=> Name ResourceID rscIdCnt
walgreen 100 1
target 200 1
sears 300 1

Second sql:
select T1.Name, T2.ResourceID,count(T2.ResourceID) as Cnt_s2
from v_Collection as T1, v_FullCollectionMembership as T2
where T1.CollectionID = T2.CollectionID
and T2.IsClient='1'
group by T1.Name, T2.ResourceID

=> Name ResourceID rscIdCnt_s2
walgreen 100 1
target 200 1

Third sql:
select T1.Name, T2.ResourceID,count(T2.ResourceID) as Cnt_s3
from v_Collection as T1, v_FullCollectionMembership as T2, v_image as T3
where T1.CollectionID = T2.CollectionID
and T2.ResourceID = T3.ResourceID
and T3.imageVersion is not null
group by T1.Name, T2.ResourceID

=> Name ResourceID rscIdCnt_s2
walgreen 100 1

HOW CAN I COMBINE THESE RESULTS LIKE

=> Name ResourceID Cnt_S1 Cnt_s2 Cnt_s3
walgreen 100 1 1 1
target 200 1 1
sears 300 1

in t-sql

Thanks for your help....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 14:44:46

[code]SELECT c.Name,cm.ResourceID,
COUNT(cm.ResourceID) AS Cnt_S1,
COUNT(CASE WHEN cm.IsClient=1 THEN cm.ResourceID ELSE NULL END) AS Cnt_S2,
COUNT(CASE WHEN i.imageVersion is not null THEN cm.ResourceID ELSE NULL END) AS Cnt_S3
FROM v_Collection c
INNER JOIN v_FullCollectionMembership cm
ON cm.CollectionID=c.CollectionID
INNER JOIN v_image i
ON i.ResourceID=cm.ResourceID
GROUP BY c.Name,cm.ResourceID[/code]

eirikr
Starting Member

18 Posts

Posted - 2008-10-31 : 15:22:16

Thank you Visakh, it's awesome
---------------------------------------

quote:

Originally posted by visakh16

SELECT c.Name,cm.ResourceID,
COUNT(cm.ResourceID) AS Cnt_S1,
COUNT(CASE WHEN  cm.IsClient=1 THEN cm.ResourceID ELSE NULL END) AS Cnt_S2,
COUNT(CASE WHEN i.imageVersion is not null THEN cm.ResourceID ELSE NULL END) AS Cnt_S3
FROM v_Collection c
INNER JOIN v_FullCollectionMembership cm
ON cm.CollectionID=c.CollectionID
INNER JOIN v_image i
ON i.ResourceID=cm.ResourceID
GROUP BY  c.Name,cm.ResourceID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 02:14:41

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources