Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?
Date: Sun, 17 Sep 2006 15:43:29 +0200
Message-ID: <eejje8$ta0$1_at_f1node01.rhrz.uni-bonn.de>
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:
- A column for aggregation should be selected in the last table instead of the id column.
- Additional conditions for selecting records from the first table can be added to the query.
- This query is used only to demonstrate the main logic (actually, it demonstrate how bad is SQL for solving such problebs).
- In order to use groupby you need to add the corresponding column and may be some other options.
-- http://conceptoriented.comReceived on Sun Sep 17 2006 - 15:43:29 CEST