Re: Box query


Mikito Harakiri wrote:
Bob Badour wrote:
Mikito Harakiri wrote:

Mikito Harakiri wrote:

Given a set of n-dimensional boxes
find all the pairs that intersect...

table boxes (
*id* integer,
dimension# integer,
low integer,
high integer
)


select b1.id as box1, b2.id as box2
from boxes b1, boxes b2
where b1.id < b2.id
and not exists (
select 1 from boxes b3, boxes b4
where b3.id = b1.id
and b4.id = b2.id
and b4.dimension# = b3.dimension#
and (b4.high < b3.low or b4.low > b3.high)
)
group by 1, 2
;

This is quite surprising answer. I didn't believe it first, and tried
to find counterexample without actually trying to figure out what your
query is doing! The reason why it looks surprising is the following.
The query is a set intersection join kind of query which is
generalization of relational division. Now, relational division
expressed in SQL is either query with 2 levels nesting subqueries, or 1
level nested subqueries with minus operator, or the query with counting
subquery in the having clause. Your query is neither of those!

To add more, there is a next question that I was going to ask: "find
the intersection volume". And your query even have the correct "group
by" clause in place. I'm really puzzled if you already knew the answer,
or put "group by" incidentally instead of "distinct":-)

.

Relevant Pages

  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)
  • Re: Different data shown based on user input
    ... my advice changes with the info that there are over 200 checkboxes. ... I suggest you use combo boxes. ... Dim strStub As String ...
    (microsoft.public.access.queries)
  • Re: Parameter query with multiple check boxes
    ... those 25 check boxes". ... Does the report get the info from the query which gets info ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • RE: Checking for null fields before running query
    ... I could use that sub and modify it so that it checks for Null in the ... return at least one record when all 3 controls have values. ... 3 cbo boxes, 2 buttons, 12 txt fields. ... the cboboxes provide parameters for a query, and also criteria for 12 DSum ...
    (microsoft.public.access.modulesdaovba)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... synchronize combo boxes to the detail section or the parameter form query ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would ...
    (microsoft.public.access.conversion)