how to combine the sql results from diff sql statements 1

[eirikr]

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....

 

[mbde]

you can join the three sql statments using a inner (or Left) join and displaying the columns from each query


Select A.Name, A.ResourceID, A.Cnt_s1, B.Cnt_s2, C.Cnt_s3
From
(select T1.Name, T2.ResourceID, count(T2.ResourceID) as Cnt_s1, T1.CollectionID
from v_Collection as T1, v_FullCollectionMembership as T2
where T1.CollectionID = T2.CollectionID
group by T1.Name,T2.ResourceID) A
left outer join
(select T1.Name, T2.ResourceID,count(T2.ResourceID) as Cnt_s2, T1.CollectionID
from v_Collection as T1, v_FullCollectionMembership as T2
where T1.CollectionID = T2.CollectionID
and T2.IsClient='1'
group by T1.Name, T2.ResourceID) B
on A.CollectionID = B.CollectionID
left outer join
(select T1.Name, T2.ResourceID,count(T2.ResourceID) as Cnt_s3, T1.CollectionID
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) C
on A.CollectionID = C.CollectionID

or
you can do a subquery

Select
T1.Name
, T2.ResourceID
, (SELECT count(ResourceID) FROM v_FullCollectionMembership WHERE v_FullCollectionMembership.CollectionID = T1.CollectionID)
, (SELECT count(ResourceID) FROM v_FullCollectionMembership WHERE v_FullCollectionMembership.CollectionID = T1.CollectionID WHERE IsClient='1')
, (SELECT count(ResourceID) FROM v_FullCollectionMembership A INNER JOIN v_image B ON A.ResourceID = B.ResourceID WHERE v_FullCollectionMembership.CollectionID = T1.CollectionID WHERE .imageVersion is not null)
FROM
v_Collection as T1
INNER JOIN
v_FullCollectionMembership as T2
ON T1.CollectionID = T2.CollectionID

both should work, note you will need a left join on the first one as all values are not avaible in the 2 and 3 query