SQL syntax for join

Question

Suppose I have two tables:

Table A has information about the stock holdings of different investment funds:

FundID   FundType   StockID   Weight
1        Small      A         0.50
1        Small      B         0.50
2        Small      B         0.25
2        Small      C         0.75
3        Large      A         1.00
4        Large      D         1.00

Table B summarizes the weight each stock has within each type of fund:

FundType  StockID  AvgWeight
Small     A        0.25
Small     B        0.375
Small     C        0.375
Large     A        0.5
Large     D        0.5

(Note that if a stock isn't listed for a fund, then the weight is assumed to be 0.)

I want to create a new table (with SQL) that has, for each FundID in Table A, all stocks that are held by any fund of the same type:

Desired results table:

FundID   FundType   StockID   Weight   AvgWeight
1        Small      A         0.50     0.25
1        Small      B         0.50     0.375
1        Small      C                  0.375
2        Small      A                  0.25
2        Small      B         0.25     0.375
2        Small      C         0.75     0.375
3        Large      A         1.00     0.5
3        Large      D                  0.5
4        Large      A                  0.5
4        Large      D         1.00     0.5

Notice that all stocks that are held by any small fund (A, B, and C) are included for each small fund (FundID is 1 or 2). Similarly, all stocks that are held by any large fund (A and D) are included in the results for each large fund.

I think I need to do some kind of outer join, but have so far been unable to figure out the SQL syntax. Any ideas? Thanks!

ypercubeᵀᴹ · Accepted Answer · 2011-11-10 16:35:01Z

2

SELECT
    ft.FundId
  , ft.FundType
  , fs.StockId
  , aa.Weight           --- or COALESCE(aa.Weight, 0) to display 0 and not NULL 
  , bb.AvgWeight
FROM 
        ( SELECT DISTINCT
              FundId
            , FundType
          FROM 
              TableA
        ) AS ft
    INNER JOIN
        ( SELECT DISTINCT
              FundType
            , StockId
          FROM 
              TableA
        ) AS fs 
            ON  fs.FundType = ft.FundType
    LEFT JOIN
        TableB AS bb
            ON  bb.FundType = fs.FundType
            AND bb.StockId = fs.StockId
    LEFT JOIN
        TableA AS aa
            ON  aa.FundId = ft.FundId
            AND aa.FundType = ft.FundType
            AND aa.StockId = fs.StockId

The INNER fs LEFT JOIN bb may be replaced with a simple INNER JOIN bb, if TableB has rows for all (FundType, StockId) combinations.

edited Nov 10, 2011 at 16:35

answered Nov 10, 2011 at 16:29

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashlin · Accepted Answer · 2011-11-10 21:29:30Z

0

Is there a reason that a CROSS JOIN would not be appropriate to solve this?

It seems like the OP is asking for all the possible valid combinations between the two tables, which is the perfect reason to CROSS, is it not?

answered Nov 10, 2011 at 21:29

Ashlin

266 bronze badges

Collectives™ on Stack Overflow

SQL syntax for join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related