Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?

pamelafluente_at_libero.it schrieb:
> 3 tables
> --------------
>
> ONE
> |
> TRANS
> |
> SUBTRANS
>
> Table: ONE
> Name
> David
> Marshall
>
> Table: TRANS
> Name Item Fact
> David IT2 1
> Marshall IT1 1
> Marshall IT1 1
>
> Table: SUBTRANS
> Item SubFact
> IT1 1
> IT2 1
> IT2 1
> IT2 1
>
> Report:
> One.[Name] AS [Name],
> count(Trans.Fact) AS [FactCount],
> count(SubTrans.SubFact) AS [SubFactCount]
>
>
> David has 1 fact and Marshall 2 facts.
>
> "questionable" select:
>
> SELECT
> One.[Name] AS [Name],
> count(Trans.Fact) AS [FactCount],
> count(SubTrans.SubFact) AS [SubFactCount]
> FROM
> One,
> Trans,
> SubTrans
> WHERE
> One.[Name] = Trans.[Name] AND
> Trans.Item = SubTrans.Item
> GROUP BY
> One.[Name];

Here is a possible problem formulation and solution.

Given a sequence of n tables T1,T2,...,TN where each next table references the previous table in its column ci, i=1,2,...,N. Find all records from the last table TN which indirectly reference records from the first table T1.

Query for 2 tables:

Deproject2 =
select T2.id
from T2, T1
where T2.c2 = T1.id // Or, alternatively, via JOIN

Solution for N tables is defined recursively:

DeprojectN =
select TN.id
from TN, (DeprojectN-1) as TN-1
where TN.cN = TN-1.id

Notes:

1. A column for aggregation should be selected in the last table instead of the id column.
2. Additional conditions for selecting records from the first table can be added to the query.
3. This query is used only to demonstrate the main logic (actually, it demonstrate how bad is SQL for solving such problebs).
4. In order to use groupby you need to add the corresponding column and may be some other options.

--
http://conceptoriented.com