Constructing an SQL Query in DB2 for SQLJ -

Constructing an SQL Query in DB2 for SQLJ -

i have table called lives:

create table lives( animal varchar(10) not null, year int not null, zoo varchar(10), primary key(animal,year) );

with given data:

animal year zoo ---------- ----------- ---------- joe 2000 joe 2001 joe 2002 d joe 2004 c fred 2002 b fred 2004 c jane 2000 jane 2001 b jane 2002 b jack 2000 jack 2001 jack 2002 d jack 2004 c judy 2004 b ruby 2003 d alfred 2006

it consists of name of animal, year , zoo in year.

i need query finds pairs (a, b) of animals have been in same zoo throughout years, , such lexicographically smaller b (i.e., < b). more precisely, such pairs (a, b) satisfy next condition: if animal lives in zoo z during year y b lives in zoo z during year y, , vice versa.

so output illustration info be:

animal animal ------- ------- jack joe

up until i've constructed query:

select l1.animal, l2.animal lives l1, lives l2 l2.year = l1.year , l1.animal > l2.animal

it gives me animals have been in zoo 1 year. don't how go on this.

i'm going utilize query in sqlj program. possible build query satisfies desired result, or should go on current query , implement rest in sqlj?

i think want exact relational division, returns pairs such 1 pair can not have zoo or year other not have.

a mutual way utilize double negation through 2 correlated sub-queries. it's bit hard follow should give right result.

-- select distinct pairs such that... select * ( select a.animal animal1, b.animal animal2 lives inner bring together lives b on a.zoo = b.zoo , a.year = b.year , a.animal < b.animal ) animals not exists ( -- there not exist animal not... select * lives b b.animal = animals.animal2 , not exists ( -- in set of animals share year , zoo select * lives c c.animal = animals.animal1 , c.zoo = b.zoo , c.year = c.year ) ) grouping animals.animal1, animals.animal2

using simple count determine equality won't work you'll match when count same though 1 animal has more zoos other. verify add together row:

animal year zoo jane 2004 b

and result accepted reply be:

animal animal jane jack joe jack joe jane

whereas solution gives:

animal1 animal2 jack joe

sql db2 sqlj