sql server - Query to fill in the missing matches -

i have following list of dispositions dialler:

create table dialleroutcomes     ([dialleroutcome] varchar(25)) ;  insert dialleroutcomes     ([dialleroutcome]) values     ('application (a)'),     ('answer machine (a)'),     ('answering machine (a)'),     ('call interested (a)'),     ('call unavailable (a)'),     ('could not debt (a)'),     ('no answer (a)'),     ('older customer (a)'),     ('answer machine (d)'),     ('no answer (d)') ;  +---------------------------+ |      dialleroutcome       | +---------------------------+ | application (a)           | | answer machine (a)        | | answering machine (a)     | | call interested (a)  | | call unavailable (a) | | not debt (a)   | | no answer (a)             | | older customer (a)        | | answer machine (d)        | | no answer (d)             | +---------------------------+ 

and following data:

create table affiliateleads     ([affiliate] varchar(10), [dialleroutcome] varchar(24)) ;  insert affiliateleads     ([affiliate], [dialleroutcome]) values     ('affiliate1', 'no answer (d)'),     ('affiliate1', 'application (a)'),     ('affiliate1', 'customer hung (a)'),     ('affiliate2', 'no answer (a)'),     ('affiliate3', 'application (a)'),     ('affiliate4', 'no answer (d)'),     ('affiliate4', 'could not (a)'),     ('affiliate3', 'no answer (d)'),     ('affiliate1', 'customer hung (a)'),     ('affiliate3', 'no pba (a)'),     ('affiliate3', 'dead line (a)'),     ('affiliate3', 'answer machine (a)'),     ('affiliate3', 'customer hung (a)'),     ('affiliate3', 'answer machine (a)'),     ('affiliate3', 'application (a)'),     ('affiliate3', 'dead line (d)'),     ('affiliate1', 'application (a)'),     ('affiliate3', 'could not (a)'),     ('affiliate2', 'application (a)'),     ('affiliate2', 'call interested (a)'),     ('affiliate2', 'customer hung (a)'),     ('affiliate1', 'call interested (a)'),     ('affiliate1', 'answer machine (a)'),     ('affiliate1', 'no answer (a)') ;  +------------+--------------------------+ | affiliate  |      dialleroutcome      | +------------+--------------------------+ | affiliate1 | no answer (d)            | | affiliate1 | application (a)          | | affiliate1 | customer hung (a)     | | affiliate2 | no answer (a)            | | affiliate3 | application (a)          | | affiliate4 | no answer (d)            | | affiliate4 | not (a)       | | affiliate3 | no answer (d)            | | affiliate1 | customer hung (a)     | | affiliate3 | no pba (a)               | | affiliate3 | dead line (a)            | | affiliate3 | answer machine (a)       | | affiliate3 | customer hung (a)     | | affiliate3 | answer machine (a)       | | affiliate3 | application (a)          | | affiliate3 | dead line (d)            | | affiliate1 | application (a)          | | affiliate3 | not (a)       | | affiliate2 | application (a)          | | affiliate2 | call interested (a) | | affiliate2 | customer hung (a)     | | affiliate1 | call interested (a) | | affiliate1 | answer machine (a)       | | affiliate1 | no answer (a)            | +------------+--------------------------+ 

i need group number of dispositions per affiliate, still show outcomes have not matched up. expected outcome:

+------------+---------------------------+--------------+ | affiliate  |          outcome          | no. of leads | +------------+---------------------------+--------------+ | affiliate1 | application (a)           |            2 | | affiliate1 | answer machine (a)        |            1 | | affiliate1 | answering machine (a)     |            0 | | affiliate1 | call interested (a)  |            1 | | affiliate1 | call unavailable (a) |            0 | | affiliate1 | not debt (a)   |            0 | | affiliate1 | no answer (a)             |            1 | | affiliate1 | older customer (a)        |            0 | | affiliate1 | answer machine (d)        |            0 | | affiliate1 | no answer (d)             |            1 | | affiliate2 | application (a)           |            1 | | affiliate2 | answer machine (a)        |            0 | | affiliate2 | answering machine (a)     |            0 | | affiliate2 | call interested (a)  |            1 | | affiliate2 | call unavailable (a) |            0 | | affiliate2 | not debt (a)   |            0 | | affiliate2 | no answer (a)             |            1 | | affiliate2 | older customer (a)        |            0 | | affiliate2 | answer machine (d)        |            0 | | affiliate2 | no answer (d)             |            0 | | affiliate3 | application (a)           |            2 | | affiliate3 | answer machine (a)        |            2 | | affiliate3 | answering machine (a)     |            0 | | affiliate3 | call interested (a)  |            0 | | affiliate3 | call unavailable (a) |            0 | | affiliate3 | not debt (a)   |            0 | | affiliate3 | no answer (a)             |            0 | | affiliate3 | older customer (a)        |            0 | | affiliate3 | answer machine (d)        |            0 | | affiliate3 | no answer (d)             |            1 | | affiliate4 | application (a)           |            0 | | affiliate4 | answer machine (a)        |            0 | | affiliate4 | answering machine (a)     |            0 | | affiliate4 | call interested (a)  |            0 | | affiliate4 | call unavailable (a) |            0 | | affiliate4 | not debt (a)   |            0 | | affiliate4 | no answer (a)             |            0 | | affiliate4 | older customer (a)        |            0 | | affiliate4 | answer machine (d)        |            0 | | affiliate4 | no answer (d)             |            1 | +------------+---------------------------+--------------+ 

i around before looking solution, nothing springs , i've been mulling on few hours before throwing in towel , asking help. know it's going fancy cross apply. appreciated in advance.

you first need cartesian product between affiliates , possible outcomes:

select  a.affiliate,         b.dialleroutcome,         isnull(count(c.dialleroutcome),0) [no. of leads] (  select distinct affiliate         dbo.affiliateleads) cross join dbo.dialleroutcomes b left join dbo.affiliateleads c     on a.affiliate = c.affiliate     , b.dialleroutcome = c.dialleroutcome group a.affiliate,          b.dialleroutcome order a.affiliate,          b.dialleroutcome;