Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!h48g2000cwc.googlegroups.com!not-for-mail From: pamelafluente@libero.it Newsgroups: comp.databases.theory Subject: Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ? Date: 17 Sep 2006 04:58:25 -0700 Organization: http://groups.google.com Lines: 54 Message-ID: <1158494305.707433.143280@h48g2000cwc.googlegroups.com> References: <1158224084.007892.62800@d34g2000cwd.googlegroups.com> <45094ae2.98.41@news2> <1158238742.439337.121720@e3g2000cwe.googlegroups.com> <1158245952.200358.109740@b28g2000cwb.googlegroups.com> <1158314405.126558.316070@b28g2000cwb.googlegroups.com> <1158323706.211856.230060@i42g2000cwa.googlegroups.com> <1158369429.438007.258820@b28g2000cwb.googlegroups.com> <1158401826.131193.54000@e3g2000cwe.googlegroups.com> <1158419725.399846.65610@k70g2000cwa.googlegroups.com> <1158420999.000849.10290@d34g2000cwd.googlegroups.com> <1158424664.022587.300380@m73g2000cwd.googlegroups.com> <1158436853.002537.22720@d34g2000cwd.googlegroups.com> <1158438063.861203.67960@e3g2000cwe.googlegroups.com> <1158443441.404136.213310@e3g2000cwe.googlegroups.com> NNTP-Posting-Host: 151.51.10.118 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1158494310 3182 127.0.0.1 (17 Sep 2006 11:58:30 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sun, 17 Sep 2006 11:58:30 +0000 (UTC) In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: h48g2000cwc.googlegroups.com; posting-host=151.51.10.118; posting-account=pU4aXg0AAACB2I--O5vo6dOgln_dPA2Z Xref: dp-news.maxwell.syr.edu comp.databases.theory:44958 Alexandr Savinov ha scritto: > pamelafluente@libero.it schrieb: > Some comments. The result is not wrong - your query returns precisely > what it has to return. And records are repeated NOT because of the > existence of 1-N relationship - they occur because your query produces > them. In this sense your problem consists in finding a way how to > express things in SQL. Yes I agree: this is exacly what I meant. The duplication "on side 1" is naturally created during the join process due to the relationship 1-N. But in a "chain" of joins if you have some sum, count, ... functions on a side 1 you may get a result that *may or may not* be "correct" (depending on your "interpretation" of the function). My aim was to devise a general way to produce these queries in such a way to be able to make the user aware of this possible problems and to provide him with the various alternatives, including the various join of subqueries when necessary. This is what reporting software usually does. > > I think that it is not a theoretical problem but the problem of The part I am talking about is the theoretical one. About a general method to create the queries and subqueries. Clearly, this is usually destined to be implemented, although I guess if could be performed manually on small designs. > implementation, i.e., how to implement certain logic. It seems that you > know what kind of result you want to get and the only difficulty is in > writing the corresponding SQL query. I do not say that it is simple. I > see several alternatives how it can be encoded in SQL including nested > queries and conventional WHERE restrictions. Yet the main difficulty is > in the performance of such queries. For a real report such a query may > take many pages and the ability to optimize it can be very important. Yes That's actually one of the many problems I have in mind and that I found out when coding these things, like also determining the optimal permutation of tables in a join under some constraints , etc... But I do not dare to ask... > > > -P > > > > http://conceptoriented.com