Re: Bidirectional Binary Self-Joins
From: David Cressey <cressey73_at_verizon.net>
Date: Sun, 01 Apr 2007 05:26:52 GMT
Message-ID: <woHPh.2094$k%2.393_at_trndny01>
> On Mar 31, 1:10 pm, "JOG" <j..._at_cs.nott.ac.uk> wrote:
> > On Mar 31, 3:37 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> >
> > > D'oh!
> >
> > > I believe it was earlier stated that each team could play
> > > only once per day, thus both (12-Dec, Hope) and
> > > (12-Dec, Calvin) must be unique. This is sufficiently
> > > annoying with RVAs that I am now inclined to go back
> > > to the non-RVA model.
> >
> > > (date, team1, team1score, team2, team2score)
> > > unique(date, team1)
> > > unique(date, team2)
> > > check(team1 < team2)
> >
> > > The third constraint's "<" can be any order relation,
> > > and canonicalizes the tuple type.
> >
> > This is a good tip - I use this sort of constraint a lot, especially
> > in self-joins to guarantee uniqueness.
> >
> > > Queries like "what games did Hope play in" and
> > > "what teams scored over 40 points" go back to
> > > being trivial.
> >
> > Unfortunately we are left with that same issue - the choice of team1
> > and team2 is arbitrary, and as David or Paul pointed out, it means I
> > have to do some jiggery pokery on my queries to consider both these
> > different attributes simultaneously, even though they are playing the
> > same roles. This obviously isn't going to kill us, but it doesn't seem
> > elegant, so it niggles.
>
> Get over it. :-)
> Seriously, though, I see the same niggles in mathematics,
> and it doesn't lead to any particular problems. Yes, it's
> not as convenient. "Big Woop" as they say on the playground.
> What games did Hope play?
> select * from games where team1 = Hope or team2 = Hope
> What teams scored over 40 points?
> OR
> you could express the symmetry as a view.
> (Is that right?)
> Then:
> What games did Hope play?
> select * from SGames where team = Hope;
> What teams scored over 40 points?
> select team from SGames where score > 40;
> Doesn't buy you all *that* much.
> Marshall
> Received on Sun Apr 01 2007 - 07:26:52 CEST
Date: Sun, 01 Apr 2007 05:26:52 GMT
Message-ID: <woHPh.2094$k%2.393_at_trndny01>
"Marshall" <marshall.spight_at_gmail.com> wrote in message
news:1175374919.556254.272810_at_n59g2000hsh.googlegroups.com...
> On Mar 31, 1:10 pm, "JOG" <j..._at_cs.nott.ac.uk> wrote:
> > On Mar 31, 3:37 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote:
> >
> > > D'oh!
> >
> > > I believe it was earlier stated that each team could play
> > > only once per day, thus both (12-Dec, Hope) and
> > > (12-Dec, Calvin) must be unique. This is sufficiently
> > > annoying with RVAs that I am now inclined to go back
> > > to the non-RVA model.
> >
> > > (date, team1, team1score, team2, team2score)
> > > unique(date, team1)
> > > unique(date, team2)
> > > check(team1 < team2)
> >
> > > The third constraint's "<" can be any order relation,
> > > and canonicalizes the tuple type.
> >
> > This is a good tip - I use this sort of constraint a lot, especially
> > in self-joins to guarantee uniqueness.
> >
> > > Queries like "what games did Hope play in" and
> > > "what teams scored over 40 points" go back to
> > > being trivial.
> >
> > Unfortunately we are left with that same issue - the choice of team1
> > and team2 is arbitrary, and as David or Paul pointed out, it means I
> > have to do some jiggery pokery on my queries to consider both these
> > different attributes simultaneously, even though they are playing the
> > same roles. This obviously isn't going to kill us, but it doesn't seem
> > elegant, so it niggles.
>
> Get over it. :-)
What if, instead of soccer, the game is Chinese checkers, where there can
be up to six contenders in one game? Or how about tournament bridge, with
say 30 pairs competing in a match?
You could end up writing an awful lot of "or" expressions.
>
> Seriously, though, I see the same niggles in mathematics,
> and it doesn't lead to any particular problems. Yes, it's
> not as convenient. "Big Woop" as they say on the playground.
>
> What games did Hope play?
>
> select * from games where team1 = Hope or team2 = Hope
>
> What teams scored over 40 points?
>> select team2 from teams where score2 > 40
> select team1 from teams where score1 > 40
> union
>
> OR
>
> you could express the symmetry as a view.
>
> create view SGames as
> select date, team1 as team, score1 as score from games union
> select date, team2 as team, score2 as score from games;
>
> (Is that right?)
>
> Then:
>
> What games did Hope play?
>
> select * from SGames where team = Hope;
>
> What teams scored over 40 points?
>
> select team from SGames where score > 40;
>
> Doesn't buy you all *that* much.
> >
> Marshall
> Received on Sun Apr 01 2007 - 07:26:52 CEST