Ok, first, what I'm trying to do, in case there's something I'm missing - I'm trying to build a query for a friend here at work that's a bit beyond her abilities. Basically, I'm working with three tables:
Table A - A table that, for a given consolidation, can provide the member accounts within that consolidation for a specified date.
Table B - A table that, for a given account, can provide a monthly return for a given date.
Table C - a table that, for a given account, can return the associated benchmark for that account (this is a non-date-specific field)
So, what I've done so far is joined Table B to Table A where A.Date = B.Date and A.Member account = B.Account, and then Table C to table A on A.Member Account = C.Account. These aren't the actual names, but they're close enough.
Here's the catch - I also want to, if possible, return the monthly return for the benchmark associated with a portfolio.
I think what I want to do (if this is possible) is nest a query within the query where it joins Table B and C where it joins C.Benchmark at B.Account, and then creates a result set of B.Account and the B.Return associated with C.Benchmark, and then query this as Table D and join that back to Table A on A.Member Account so for a given composite, when it returns A.Member Account I can also return something like "D.Benchmark Return" where D.Benchmark Return is B.Monthly Return for C.Benchmark. But, I have no clue how to do this, and I'm not really sure what to ask Google.
Sure - inline views are AWESOME tools. There's a couple of different approaches depending on what your flavor of SQL supports. The easy one would be this:
select a.member_account, d.benchmark_return from (table name) a, (select benchmark_return, a.account account from (select b.return return, c.benchmark benchmark from b, c where b.account = c.account)) d where a.account = d.account and a.date = (whatever the range is)
If I'm reading you right, this should give your return joined to account by the date range, which is what you want. The important bit is this:
(select benchmark_return, a.account account from (select b.return return, c.benchmark benchmark from b, c where b.account = c.account)) d
Where your query becomes an inline view, i.e. a table that can be selected against.