1

I have a 3-table schema. Two of the tables (Trade/Portfolio) have a 1:1 relationship, so the FK on one of these tables has the unique constraint.

The table, as explained above, with the FK (which is Portfolio) relates to a third table. As this third table (Price) is displaying historical information for a Portfolio (there can be many prices for a portfolio over a time-period), there's a bog-standard 1:m relationship.

However, I need to get the various prices for a portfolio. That's easy with a query which works on the portfolio ID. However, is this a feasible way to get the price of a single trade? Is there any limitation in the design that would prevent this?

Apologies for the long title, but could not find a better way to explain the issue!

Thanks

GurdeepS
  • 65,107
  • 109
  • 251
  • 387
  • 1
    Reading your question, I'm _sure_ that what you want can be done, and in a simple, straightforward way. Unfortunately, you aren't very clear on the exact table relationships/structures. Could you re-word that part? – Joel Coehoorn Feb 27 '11 at 20:03
  • See these questions: do they help understanding JOIN orders? http://stackoverflow.com/questions/5009573 and http://stackoverflow.com/questions/3899715 – gbn Feb 27 '11 at 20:06
  • trade inner join to portfolio left join to price if I understood your design correctly. – Cem Güler Feb 27 '11 at 20:09

1 Answers1

1

By your description I guess this is your data model. FK TradeID is a unique in Portfolio. enter image description here

And you wonder if it is possible to get the rows from Price related to Trade. Here is a query that will give you all rows from Price where TradeID is 1.

select Price.*
from Portfolio
  inner join Price
    on Portfolio.PortfolioID = Price.PortfolioID
where Portfolio.TradeID = 1

I see nothing in this design that will prevent you from fetching the rows from Price given a TradeID.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281