0

I need to bring in data from 3 different views into my results.

View A has almost all the data I need but it uses primary keys for two fields and I need to pull in the identifiers from the UI so it looks like this:

View A: PortfolioID, SecurityID, then 20 other points I need

View B: PortfolioID, PortfolioCode,

View C: SecurityID, Symbol

I need to select all the data from View A but instead of PortolioID I need the corresponding PortfolioCode from View B and instead of SecurityID I need the corresponding symbol from View C.

Sorry if this is very basic, I'm a complete newby to SQL.

Thanks!

bummi
  • 27,123
  • 14
  • 62
  • 101
Josh Wuerthele
  • 1

2 Answers2

0

You need to use joins in order to accomplish what your looking for. 

 SELECT ViewA.PortfolioID, ViewA.SecurityID, ViewA.*, ViewB.PortfolioCode, ViewC.Symbol 
    FROM ViewA
    INNER JOIN ViewB on ViewB.PortfolioID = ViewA.PortfolioID
    INNER JOIN ViewC on ViewC.SecurityID = ViewA.SecurityID

You can learn more about the different types of joins here

Kevin Kunderman
  • 2,036
  • 1
  • 19
  • 30
0

Try something like this:



    SELECT a.*, b.PortfolioCode, c.Symbol
    FROM ViewA a
    INNER JOIN ViewB b ON (a.PortfolioID = b.PortfolioID)
    INNER JOIN ViewC c ON (a.SecurityID = c.SecurityID)
jinsky
  • 1
  • I was able to get this to work. One issue I'm running into that I didn't think of is that every transaction has a debit and credit. Therefore I actually have securityID1 and securityID2 in view A. Both need to pull a symbol from the same view (c) hich has securityid as the key. The inner join of both ID1 and ID2 to c.SecurityId doesn't work. – Josh Wuerthele May 31 '13 at 15:11
  • Try this, if I'm understanding the structure of the views correctly. ` SELECT a.*, b.PortfolioCode, c1.Symbol AS SymbolID1, c2.Symbol AS SymbolID2 FROM ViewA a INNER JOIN ViewB b ON (a.PortfolioID = b.PortfolioID) INNER JOIN ViewC c1 ON (a.SecurityID = c1.SecurityID1) INNER JOIN ViewC c2 ON (a.SecurityID = c2.SecurityID2)` – jinsky May 31 '13 at 16:00