I'm trying get some summary results from two related tables. I'm looking to find the most efficient way to return this data in one result set.
The tables can be summed up as follows:
MainTable MainID INT, Description VARCHAR(50) Eg: MainID Description 1 John 2 Adam 3 Fred ChildTable ChildID INT, ParentID INT, IsImportant BIT, HasBeenUsed BIT Eg: ChildID ParentID IsImportant HasBeenUsed 101 1 1 0 102 1 1 1 103 2 0 0 104 2 1 0 The results that I want are as follows: MainID Description TotalChild AllImportant AllUsed 1 John 2 2 1 2 Adam 2 1 0 3 Fred 0 0 0
I have tried using common table expressions and that appears to work fine if all I want is the AllImportant but when I try to add the AllUsed it goes wrong.
Can anyone put me on the right path as I feel I'm getting a bit lost on this one.