Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML]

David:
I'm missing something here.  Why can't you create another, separate logical table that joins the
3 tables, EMP, DEPT and BUDGET?
All the best, Ashok

On 2/2/2011 5:33 AM, David McNeil wrote:
>
>     Actually, an example would be good, because I do not understand what you mean by 'sub-logical-table'
>
>
> The R2RML spec allows logical tables to be defined as SQL queries. So a "sub-logical-table" is really just a sub-query. What I am getting at is that R2RML allows the creation of an abstraction: a SQL query as a logical table, but it does not allow these logical tables to be composed together like regular tables. Regular tables can be composed by writing a SQL query that joins them together.
>
> So for example, if a database has an EMPLOYEE and a DEPT table then I can compose them in a logical table like this:
>
> SELECT
> EMPLOYEE.NAME <http://EMPLOYEE.NAME>, DEPT.DEPT_NAME
> FROM
>     EMPLOYEE JOIN DEPT ON EMPLOYEE.DEPT_NUMBER = DEPT.DEPT_NUMBER
>
> Let's call this logical table "EMP_DEPT".
>
> I would like to create another logical table by joining EMP_DEPT with another real table named BUDGET:
>
> SELECT
> EMP_DEPT.NAME <http://EMP_DEPT.NAME>, BUDGET.AMOUNT
> FROM
>     EMP_DEPT JOIN BUDGET on EMP_DEPT.DEPT_NAME = BUDGET.DEPT_NAME
>
> Allowing the creation of abstractions that can be composed together unleashes great power in the mapping language. Currently logical tables in some ways emulate real tables, but they cannot be combined as shown above. By allowing them to be combined we are making them more like real tables. The immediate benefit is that this approach allows SQL queries to be reused as sub-queries in multiple, different contexts without copying-and-pasting them.
>
> As a practical matter, I often see database models where several tables must be joined together to produce a desired high-level view of the data for consumption by users. Allowing these joins to be expressed once and reused keeps the mappings trim and avoids duplication.
>
> -David