CODEWITHSUNDEEP
sqlsql-serverjoinleft-join
ihatemash
ihatemash

Reputation: 1504

Aggregate functions as column results from multiple tables

I have the following table structures:

Table1
--------------
Table1Id
Field1
Field2

Table2
------------
Table2Id
Table1Id
Field1
Field2

Table3
-----------
Table3Id
Table1Id
Field1
Field2

I need to be able to select all fields in Table1, count of records in Table2, and count of records in Table3 Where count of records in Table2 > count of records in Table3

Here is an example of expected output with the given data:

Table1 Data
-------------
1     Record1Field1     Record1Feild2
2     Record2Field1     Record2Feild2
3     Record3Field1     Record3Feild2
4     Record4Field1     Record4Feild2 

Table2 Data
------------
1      1     Record1Field1     Record1Feild2
2      1     Record2Field1     Record2Feild2
3      2     Record3Field1     Record3Feild2
4      2     Record4Field1     Record4Feild2
5      2     Record5Field1     Record5Feild2
6      4     Record6Field1     Record6Feild2
7      4     Record6Field1     Record6Feild2
8      4     Record6Field1     Record6Feild2

Table3 Data
------------
1      2     Record1Field1     Record1Feild2   
2      2     Record2Field1     Record2Feild2   
3      3     Record3Field1     Record3Feild2   
4      3     Record4Field1     Record4Feild2   
5      3     Record5Field1     Record5Feild2   
6      4     Record6Field1     Record6Feild2

Desired Results

Table1Id     Field1         Field2            Table2Count     Table3Count
1            Record1Field1  Record1Field2     2               0
2            Record2Field1  Recird2Field2     3               2
4            Record4Field1  Recird4Field2     3               1

Notice record 3 in Table 1 is not shown because the record count in Table2 is less than the record count in Table3. I was able to make this work using a very ugly query similar to the one below but feel there is a much better way to do this using joins.

SELECT
    t1.Table1Id,
    t1.Field1,
    t1.Field2
    (Select Count(Table2Id) From Table2 t2 Where t2.Table1Id = t1.Table1Id) as Table2Count,
    (Select Count(Table3Id) From Table3 t3 Where t3.Table1Id = t1.Table1Id) as Table3Count,
From 
    Table1 t1
Where
    (Select Count(Table2Id) From Table2 t2 Where t2.Table1Id = t1.Table1Id)  >  (Select Count(Table3Id) From Table3 t3 Where t3.Table1Id = t1.Table1Id)

Upvotes: 0

Views: 48

Answers (2)

clinomaniac
clinomaniac

Reputation: 2218

Hard to test it without working examples but something along these lines should be a good starting point.

SELECT
    t1.Table1Id,
    t1.Field1,
    t1.Field2,
    COUNT(DISTINCT t2.Table2Id),
    COUNT(DISTINCT t3.Table3Id)
From Table1 t1 
    LEFT OUTER JOIN Table2 t2 ON t1.Table1Id = t2.Table1Id
    LEFT OUTER JOIN Table3 t3 ON t1.Table1Id = t3.Table1Id
GROUP BY t1.Table1Id
HAVING COUNT(DISTINCT t2.Table2Id) > COUNT(DISTINCT t3.Table3Id)

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133410

You could get all the value in t1 and the data form t2 e t3 for your comparision using a couple of join on grouped values

SELECT
     t1.Table1Id
    ,t1.Field1
    ,t1.Field2
    , tt2.count_t2
    , tt3.count_t3
from table1 t1
join  (
  select Table1Id, count(*)  count_t2
  From Table2
  group by Table1Id
) tt2 on tt2.Table1Id  = t1.Table1Id
join  (
  select Table1Id, count(*)  count_t3
  From Table3
  group by Table1Id
) tt3 on tt3.Table1Id  = t1.Table1Id
where tt2.count_t2 < tt3.count_t3 <

Upvotes: 0

Related Questions