sql - How to create a view which has consolidated information from multiple tables? -

i've made example similar situation 1 i'm working on.

input tables:

team table

teamid | teamname =================      1 | alpha      2 | beta      3 | charlie      4 | delta 

member table

teamid | memberid | membername | age ====================================      1 |        1 | anne       |  10      1 |        2 | bob        |  20      2 |        1 | carol      |  30      2 |        2 | david      |  40      3 |        1 | elaine     |  30      3 |        2 | fred       |  20      4 |        1 | geoff      |  50 

job table

teamid | jobid | earned | status ===================================      1 |     1 |    50  | complete      1 |     2 |    75  | inprogress      2 |     1 |    80  | complete      3 |     1 |    50  | inprogress 

i'm trying create view has consolidated information 3 input tables:

teamid | teamname | isabove35 | hasbeenpaid | alljobscomplete ===============================================================      1 | alpha    |         0 |           1 |                0      2 | beta     |         1 |           1 |                1      3 | charlie  |         0 |           1 |                0      4 | delta    |         1 |           0 |                0 

isabove35 should contain 1 if of team members above 35 years in age (otherwise 0)

hasbeenpaid should contain 1 if earnings team more 0

alljobscomplete should contain 1 if team has job entries , complete

i can work out how of parts individually, e.g.

isabove35

select t.teamid, case when m.age null 0 else 1 end isabove35 team t left outer join member m on t.teamid = m.teamid , age > 35 

hasbeenpaid

select t.teamid, case when sum(earned) > 0 1 else 0 end hasbeenpaid team t left outer join job j on t.teamid = j.teamid group t.teamid 

but i'm not sure how calculate field 'alljobscomplete' , how put columns 1 view.

any appreciated!

one way of doing using correlated sub-queries in case statements:

select  *  , case when exists      (select * dbo.member m m.teamid = t.teamid , m.age > 35)     1 else 0 end isabove35  , case when (select sum(earned) job j j.teamid = t.teamid) > 0    1 else 0 end hasearnings , case when exists     (select * job j j.teamid = t.teamid , status = 'complete')     , not exists      (select * job j j.teamid = t.teamid , status <> 'complete')    1 else 0 end alljobscomplete dbo.team t 

sqlfiddle demo