How do I merge these two queries so that the result set is “combined” and consistent?

Question

Please consider my first query:

SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
INNER JOIN research..complex rc
ON cf.cnum = rc.cnum
WHERE rs.date  = '9/30/2016'
GROUP BY rc.name, cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
ORDER BY cf.cnum

This gives me the following resultset:

Here is my second query:

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

Which gives me the following resultset:

I've been trying so many different variations of this and joining the relevant tables together, but I am not able to get a consistent result. What I eventually want is a resultset with four columns:

cnum | name | sums | assets

Please help me with this.

Answer 1

Being somewhat lazy the easiest way to handle it is with a FULL OUTER JOIN and subqueries. I'm making the assumption this is SQL Server btw.

SELECT ISNULL(x.cnum, y.cnum) cnum, ISNULL(x.name, y.name) name,
    x.sums, y.assets
(
    SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
    FROM retirement_survey as rs
    INNER JOIN currentfunds as cf
    ON rs.fundid = cf.fundid
    INNER JOIN research..complex rc
    ON cf.cnum = rc.cnum
    WHERE rs.date  = '9/30/2016'
    GROUP BY rc.name, cf.cnum
    HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
) x
FULL OUTER JOIN
(
    SELECT r.cnum as cnum, c.name as name, r.assets as assets
    FROM research..complex c, research..retirement_simple_ira r
    where r.date = '9/30/2016'
    and c.cnum = r.cnum
) y
    ON x.cnum = r.cnum
ORDER BY ISNULL(x.cnum, y.cnum)

Answer 2

Assuming your tables are defined the following way (I have made some smallish naming changes, and assumed lots of things):

 CREATE TABLE current_funds
 (
     fundid integer PRIMARY KEY,
     cnum integer NOT NULL,
     UNIQUE (cnum)
 ) ;

 CREATE TABLE retirement_survey
 (
    fundid integer NOT NULL REFERENCES current_funds (fundid),
    "date" date,
    dh_simple integer NOT NULL,
    ha_simple integer DEFAULT 0
 ) ;

 CREATE TABLE research_complex
 (
     cnum integer REFERENCES current_funds (cnum),
     name varchar(50)
 ) ;

 CREATE TABLE research_retirement_simple_ira
 (
    cnum integer REFERENCES current_funds (cnum),
    "date" date,
    assets integer
 ) ;

With some slight naming changes, the following query should work for you:

SELECT
    coalesce(s1.cnum, s2.cnum) AS cnum, 
    coalesce(s1.name, s2.name) AS name, 
    s1.sums, 
    s2.assets
FROM
    (SELECT 
        cf.cnum as cnum, 
        rc.name as name, 
        SUM(rs.dh_simple + rs.ha_simple) as sums
    FROM 
        retirement_survey as rs
        INNER JOIN current_funds as cf ON rs.fundid = cf.fundid
        INNER JOIN research_complex rc ON cf.cnum = rc.cnum
    WHERE 
        rs.date  = '20160930'
    GROUP BY 
        rc.name, cf.cnum
    HAVING 
        SUM(rs.dh_simple + rs.ha_simple) > 0
    ) AS s1
    FULL OUTER JOIN
    (SELECT 
        rc.cnum as cnum, 
        rc.name as name, 
        r.assets as assets
    FROM 
        research_complex rc
        JOIN research_retirement_simple_ira r ON r.cnum = rc.cnum
    WHERE 
        r.date = '20160930'
    ) s2
    ON s1.cnum = s2.cnum 
ORDER BY 
    cnum ;

And give you the following result:

 cnum | name                                        |    sums |     assets
 ---: | :------------------------------------------ | ------: | ---------:
    2 | Lord, Abbett & Co. LLC                      |  329929 |  303026683
    7 | Capital, Research & Management              |    null |   20000000
   14 | Principal                                   |  867280 |  777135509
   15 | Bridges                                     |     430 |       null
   35 | Delaware Investments                        |  147950 |       null
   38 | Bank of New York Mellon/Dreyfus Corporation |    1417 |       null
   43 | Neuberger Berman Management LLC             |   14273 |       null
   45 | Federated Investors                         |    null |   67583161
   46 | Fidelity Investments                        | 7676456 |   20000000
   47 | Foresters Financial                         |   78766 |       9999
   49 | Calvert Investments                         |   76591 |       null
   51 | Franklin Templeton Investments              |    null | 1848513179
   55 | William Blair Funds                         |     233 |       null
   59 | Columbia Threadneedle Investments           |  138908 |  138678936
   68 | MFS Investment Management                   | 1551971 | 1518052790
   71 | Nationwide Funds                            |   10183 |       null
   80 | OppenheimerFunds/MassMutual                 | 4101977 |   20000000
   85 | Pioneer Investment Management USA Inc.      |    null |  289936684
   88 | T. Rowe Price                               | 1905810 | 1921051937
   91 | Putnam Funds                                |  169569 |   20000000
  102 | State Farm                                  |  999378 | 1026211854
  110 | Waddell & Reed Funds                        |  700310 |  560233847
  115 | Vanguard Group                              | 6938415 |   20000000
  130 | USAA                                        |  169139 |  168430000
  143 | Invesco                                     | 1822865 | 1798640460
  149 | Affiliated Managers Group                   |     102 |       null
  153 | Goldman Sachs & Co.                         |  182038 |       null
  156 | TCW Funds, Inc.                             |       3 |       null
  169 | Davis Selected Advisers, L.P.               |   16633 |       null
  200 | Deutsche Asset Management                   |  236911 |       null
  207 | Hartford Funds                              | 1047561 |  586878824
  226 | Meeder Asset Management, Inc.               |    1487 |       null
  236 | Nuveen Investments                          |   33764 |       null
  275 | SunAmerica Group                            |   69726 |       null
  280 | Janus                                       |    1899 |       null
  286 | Legg Mason                                  |    5533 |       null
  302 | Transamerica Asset Management               |  308727 |       null
  304 | John Hancock Investments                    |    null |  342692928
  379 | JP Morgan Chase & Co.                       |    null |  148135910
  530 | Schwab Funds, Laudus Funds & Schwab ETFs    |    null |   20000000
 

You can check all the setup at dbfiddle (checked with SQL Server settings, which are quite close to Sybase; it also works with PostgreSQL settings).

NOTE: This is, basically, the same approach taken by @kenneth-fisher... just with an added proof of concept.

---

If my assumptions about the structure of your tables are correct, the following query will return the same result and be slightly more efficient:

SELECT
    s3.cnum, rc.name, s3.sums, s3.assets
FROM
    (SELECT
        coalesce(s1.cnum, s2.cnum) AS cnum, s1.sums, s2.assets
    FROM
        (SELECT 
            cf.cnum as cnum, 
            SUM(rs.dh_simple + rs.ha_simple) as sums
        FROM 
            retirement_survey as rs
            INNER JOIN current_funds as cf ON rs.fundid = cf.fundid
        WHERE 
            rs.date  = '20160930'
        GROUP BY 
            cf.cnum
        HAVING 
            SUM(rs.dh_simple + rs.ha_simple) > 0
        ) AS s1
        FULL OUTER JOIN
        (SELECT 
            rc.cnum as cnum, 
            r.assets as assets
        FROM 
            research_retirement_simple_ira
        WHERE 
            r.date = '20160930'
        ) s2
        ON s1.cnum = s2.cnum 
    ) s3
    JOIN research_complex rc ON rc.cnum = s3.cnum
ORDER BY 
    cnum ;

Check it also at dbfiddle.