2

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.

  • Have you looked at UNION or UNION ALL? – Erik Darling May 11 '17 at 1:08
  • In my naivete, I placed a UNION between the two statments - but that seems way too easy and didn't seem to work. – Ebony Maw May 11 '17 at 1:12
2

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)
  • Specifically, it's Sybase, but it should work, I think? – Ebony Maw May 11 '17 at 1:17
  • I got the following errors: imgur.com/a/ylFVU – Ebony Maw May 11 '17 at 1:23
  • I'm afraid I don't know sybase syntax. And from what I'm seeing on a quick search it can't handle a full outer join. The point of the full outer join is in case there are rows from either side that aren't in the other query. Is this a possibility? Any chance they will both always have all of the cnums? – Kenneth Fisher May 11 '17 at 1:33
  • In our datasets, there are instances where some cnums may not exist in the other dataset. To do a full outer join in Sybase, someone suggested this: "UNION a left outer join and a right outer join" but I'm not sure what that means as I'm a SQL novice. – Ebony Maw May 11 '17 at 1:38
  • 1
    In other words change the FULL OUTER JOIN to a LEFT OUTER JOIN, then before the ORDER BY put a UNION and a copy of the whole thing over again but with a RIGHT OUTER JOIN. To start try just the LEFT OUTER JOIN to make sure the syntax is correct. – Kenneth Fisher May 11 '17 at 1:51
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.