SQL Server - Merge multiple query results into one result set

sql-server, sql-server-2008-r2

Solution

WITH SpaceRow AS (
    SELECT tenantid
          ,name
          ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER BY id) AS RowNumber
    FROM Space
)
,ContractRow AS (
    SELECT tenantid
          ,id
          ,ROW_NUMBER() OVER (PARTITION BY tenantid ORDER BY id) AS RowNumber
    FROM Contracts
)
,SpaceContracts AS (
    SELECT COALESCE(SpaceRow.tenantid, ContractRow.tenantid) AS tenantid
          ,COALESCE(SpaceRow.RowNumber, ContractRow.RowNumber) AS RowNumber
          ,SpaceRow.name AS SpaceName
          ,ContractRow.id AS ContractId
    FROM SpaceRow
         FULL OUTER JOIN ContractRow
              ON SpaceRow.tenantid = ContractRow.tenantid
                 AND SpaceRow.RowNumber = ContractRow.RowNumber
)
SELECT CASE WHEN SpaceContracts.RowNumber IS NULL  
                 OR SpaceContracts.RowNumber = 1
            THEN Tenant.name 
            ELSE NULL
        END AS TenantName
       ,SpaceContracts.SpaceName
       ,SpaceContracts.ContractId
FROM Tenant
     LEFT JOIN SpaceContracts
         ON SpaceContracts.tenantid = Tenant.id
ORDER BY Tenant.id
        ,SpaceContracts.RowNumber

Problem

I have three queries which return one columns each. ``` SELECT Name FROM Tenant SELECT Name FROM Space SELECT ID FROM Contracts ``` The table definitions are: ``` Tenant (ID, Name) Space (ID, Name, TenantID) Contracts (ID, TenantID) ``` The information that I 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 I write a query to achieve the 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 and Contracts table, but I don't want them to take into account the spaces between tenants and I don't want the values in any of the columns to be duplicate. I've tried using joins, but they obviously duplicate the values in the columns if matches exist between them. ``` SELECT T.NAME 'Tname', S.NAME 'Sname', C.ID FROM Tenant T LEFT JOIN Space S ON T.ID = S.TenantID LEFT JOIN Contracts C ON T.ID = C.TenantID ``` I've also tried correlating this into a subquery and using `ROW_NUMBER()` and combining with some `CASE` statements to achieve the desired format, but wasn't very successful. Here is a SQLFiddle with some sample data. Any suggestions/comments or links that could help are very much appreciated.

Original source