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

sybase

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:

enter image description here

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:

enter image description here

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.

Best Answer

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)