Constructing an SQL Query in DB2 for SQLJ -

I have a table called Live:

  Make table live (animal varchar (10 ) No faucet, Insert tap of the year, Zoo (10), Primary key (animal, year));  

with the given data:

  Anem year year ---------- ---------- - ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Judy 2004 b Rabbi 2003 de Alfred 2006 a  

The animal was in the year, and the year of the zoo was in that year.

I need a query that gets the pair of all the animals (A, B) which have always been in the same zoo all year round, and such as B (e.g., A and Lieutenant B ) Is grammatically smaller. More precise, such pairs (A, B) satisfy the following condition: If there is a life of livestock in the zoo during the year, then it remains in the zoo during the same year, and on the contrary.

Then the output will be for my example data:

  Animal Animals ------- ------- Jack Joe  

So far I have created this query:

  SELECT l1.animal, L2 as life from loneliness, L2 as life, L2 as life, WHERE l2 Year = l1.year and L1. Animal & gt; L2.animal  

This gives me the animals living in the zoo for 1 year. I do not have to continue how to do this now.

I am going to use this query in my SQL programs. Is it possible to create a query that satisfies the results that I want, or would I like to go through my current query and apply the balance to sqlj?

I think what you want is an exact relational division, which gives to all pairs like That can not be a zoo or year in a pair which is not near to another.

A common way to do this is to use a double rejection through two correlated sub-questions, it is a bit hard to follow, but you should give the right result.

  - All different pairs such as select ... SELECT * FROM (SELECT a.animal AS animal1, b.animal As animals, one lives a life of 2 lives. Zoo = b.zoo and a.year = b.year and a.animal & lt; b.animal live in animals where there are no present (no animal exists ... SELECT * B.Annal = Animals: Animal 2 or none of the existing (- which is part of the year of the animal and is part of the zoo) is selected from life c.animal = animals.animal1 and c.zoo = B.zoo and c .year = c.year)) know Group by s. Animal 1, animals Animal 2  

Using a simple calculation to determine the similarity will not work for you. A match is similar when one animal has more zoos than the other. To confirm this, add this line:

  Animal year zhu jen 2004 b  

and the results will be received from an approved answer:

  Animal animal Jane Jack Jack Jack Jane  

While my solution:

  Animals 1 Animals 2 Jack Jack  >