create table #Topic  (
        TopicID   char(2)     not null primary key ,
        TopicName varchar(32) not null
)
create table #Seminar (
        SeminarID int     not null primary key ,
        TopicID   char(2) not null foreign key references #Topic(#TopicID)
)
create table #Registrant ( 
        RegistrantID int not null primary key ,
        SeminarID    int not null foreign key references Seminar(SeminarID)
)
go
insert into #Topic values ( 'BR','How to be the Best Receptionist' )
insert into #Topic values ( 'AC','Advanced Coding'                 )
insert into #Topic values ( 'IP','Insurance Processing'            )

insert into #Seminar values ( 1 , 'BR' )
insert into #Seminar values ( 2 , 'IP' )
insert into #Seminar values ( 3 , 'AC' )
insert into #Seminar values ( 4 , 'AC' )
insert into #Seminar values ( 5 , 'BR' )
insert into #Seminar values ( 6 , 'BR' )

insert into #Registrant values ( 1  , 1 )
insert into #Registrant values ( 2  , 2 )
insert into #Registrant values ( 3  , 1 )
insert into #Registrant values ( 4  , 3 )
insert into #Registrant values ( 5  , 1 )
insert into #Registrant values ( 6  , 2 )
insert into #Registrant values ( 7  , 4 )
insert into #Registrant values ( 8  , 2 )
insert into #Registrant values ( 9  , 3 )
insert into #Registrant values ( 10 , 1 )

--The sample data:
SELECT *
FROM #Topic AS t
JOIN #Seminar AS s
        ON s.TopicID = t.Topicid
JOIN #Registrant r
        ON r.seminarid = s.seminarid
ORDER BY t.Topicid, s.seminarid , r.registrantid
/*
TopicID TopicName                        SeminarID   TopicID RegistrantID SeminarID   
------- -------------------------------- ----------- ------- ------------ ----------- 
AC      Advanced Coding                            3 AC                 4           3 
AC      Advanced Coding                            3 AC                 9           3 
AC      Advanced Coding                            4 AC                 7           4 
BR      How to be the Best Receptionist            1 BR                 1           1 
BR      How to be the Best Receptionist            1 BR                 3           1 
BR      How to be the Best Receptionist            1 BR                 5           1 
BR      How to be the Best Receptionist            1 BR                10           1 
IP      Insurance Processing                       2 IP                 2           2 
IP      Insurance Processing                       2 IP                 6           2 
IP      Insurance Processing                       2 IP                 8           2 
*/

-- Problem: To show each topic , distinct seminars in each topic & 
--          the distinct registrations in each topic.
SELECT t.TopicID   ,
       t.TopicName ,
       ( SELECT COUNT( DISTINCT s.SeminarID )
         FROM #Seminar AS s
         WHERE s.TopicID = t.TopicID ) AS  Seminars  ,
       ( SELECT COUNT( DISTINCT r.RegistrantID )
         FROM #Registrant AS r
         JOIN #Seminar AS s
         ON r.SeminarID = s.SeminarID
         WHERE s.TopicID = t.TopicID ) AS Registrants
FROM #Topic AS t
ORDER BY t.TopicID
/*
TopicID TopicName                        Seminars    Registrants 
------- -------------------------------- ----------- ----------- 
AC      Advanced Coding                            2           3 
BR      How to be the Best Receptionist            3           4 
IP      Insurance Processing                       1           3 
*/

This page was last updated on May 01, 2006 04:28 PM.