Re: Bidirectional Binary Self-Joins

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?

  select team1 from teams where score1 > 40     union
  select team2 from teams where score2 > 40

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 Sat Mar 31 2007 - 23:01:59 CEST