SQL Server - Merge multiple query results into one result set -

i have 3 queries return 1 columns each.

select name tenant select name space select id contracts 

the table definitions are:

tenant    (id, name) space     (id, name, tenantid) contracts (id, tenantid) 

the information have in these tables is:

+----+---------+ | id |  name   | +----+---------+ |  1 | tenant1 | |  2 | tenant2 | |  3 | tenant3 | +----+---------+  +----+------+----------+ | id | name | tenantid | +----+------+----------+ |  1 | s1   |        1 | |  2 | s2   |        1 | |  3 | s3   |        2 | |  4 | s4   |        3 | |  5 | s5   |        3 | +----+------+----------+  +----+----------+ | id | tenantid | +----+----------+ |  1 |        1 | |  2 |        1 | |  3 |        2 | |  4 |        2 | |  5 |        2 | |  6 |        3 | +----+----------+ 

how can write query achieve below structure?

+----------+-------+----------+ |  tenant  | space | contract | +----------+-------+----------+ | tenant 1 | s1    |        1 | |          | s2    |        2 | | tenant 2 | s3    |        3 | |          |       |        4 | |          |       |        5 | | tenant 3 | s4    |        6 | |          | s5    |          | +----------+-------+----------+ 

i have links between tenant , contracts table, don't want them take account spaces between tenants , don't want values in of columns duplicate.

i've tried using joins, duplicate values in columns if matches exist between them.

select t.name 'tname',     s.name 'sname',     c.id tenant t left join space s     on t.id = s.tenantid left join contracts c     on t.id = c.tenantid 

i've tried correlating subquery , using row_number() , combining case statements achieve desired format, wasn't successful.

here sqlfiddle sample data.

any suggestions/comments or links appreciated.

with spacerow (     select tenantid           ,name           ,row_number() on (partition tenantid order id) rownumber     space ) ,contractrow (     select tenantid           ,id           ,row_number() on (partition tenantid order id) rownumber     contracts ) ,spacecontracts (     select coalesce(spacerow.tenantid, contractrow.tenantid) tenantid           ,coalesce(spacerow.rownumber, contractrow.rownumber) rownumber           ,spacerow.name spacename           ,contractrow.id contractid     spacerow          full outer join contractrow               on spacerow.tenantid = contractrow.tenantid                  , spacerow.rownumber = contractrow.rownumber ) select case when spacecontracts.rownumber null                    or spacecontracts.rownumber = 1             tenant.name              else null         end tenantname        ,spacecontracts.spacename        ,spacecontracts.contractid tenant      left join spacecontracts          on spacecontracts.tenantid = tenant.id order tenant.id         ,spacecontracts.rownumber