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.
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.