Hi,

I need to achieve the following.
1 X table called ANIMAL
Fields

ANIMAL.ID
ANIMAL.IDENTIFICATION --UNIQUE ANIMAL NAME
ANIMAL.DOB
ANIMAL.GENDER ('F' or 'M')
ANIMAL.DAMIDENTIFICATION -- REFERS TO ANIMAL.IDENTIFICATION ie the mother of the animal.

I want all animals that have NOT yet Calved. ie they have no DAMIDENTIFICATIONs that refer to them.

I try:
Select D.ANIMALID, D.ANIMALIDENTIFICATION as DAMIDENTIFICATION, D.ANIMALGENDER, D.ANIMALDOB,
C.ANIMALDAMID, C.ANIMALIDENTIFICATION as CALFIDENTIFICATION
FROM ANIMAL D
LEFT JOIN ANIMAL C
ON D.ANIMALIDENTIFICATION = C.ANIMALDAMID
WHERE D.ANIMALGENDER = 'F'
ORDER BY C.ANIMALDAMID

By ordering by C.ANIMALDAMID, all the records with no 'Right' values ie padded with 'NULL' are listed first.

I'm actually only interested in these, ie these animal have not yet calved.

Here follows the output of the above query:
ANIMALID DAMIDENTIFICATION ANIMALGENDER ANIMALDOB ANIMALDAMID CALFIDENTIFICATION
--------------------------------------------------------------------------------------------------
4410 NGIFAW 060032 F 2006/11/16
4408 NGIFAW 060027 F 2006/11/08
4444 NGIFAW 060039 F 2006/12/20
4419 NGIFAW 070040 F 2007/11/26
4420 NGIFAW 070041 F 2007/11/30
4338 NGIFAW 070012 F 2007/05/05
4421 NGIFAW 070042 F 2007/12/03
4422 NGIFAW 070043 F 2007/12/05
4447 NGIFAW 070004 F 2007/01/08
4448 NGIFAW 070006 F 2007/01/19
4375 NGIFAW 070016 F 2007/06/20
4398 NGIFAW 070036 F 2007/10/23
4449 NGIFAW 070008 F 2007/02/01
4456 NGIFAW 080010 F 2008/04/14
4441 NGIFAW 080001 F 2008/01/01
4457 NGIFAW 080011 F 2008/05/04
4470 NGIFAW 080021 F 2008/09/17
4443 NGIFAW 080003 F 2008/01/27
4459 NGIFAW 080013 F 2008/06/25
4483 NGIFAW 080033 F 2008/11/01
4473 NGIFAW 080024 F 2008/10/01
4485 NGIFAW 080034 F 2008/11/03
4461 NGIFAW 080015 F 2008/08/13
4486 NGIFAW 080035 F 2008/11/08
4487 NGIFAW 080036 F 2008/12/20
4454 NGIFAW 080008 F 2008/03/25
4477 NGIFAW 080028 F 2008/10/14
4455 NGIFAW 080009 F 2008/03/29
4468 NGIFAW 080019 F 2008/09/09
4511 NGIFAW 090020 F 2009/12/02
4513 NGIFAW 090021 F 2009/12/02
4489 NGIFAW 090002 F 2009/04/26
4515 NGIFAW 090033 F 2009/12/02
4494 NGIFAW 090007 F 2009/09/23
4508 NGIFAW 090019 F 2009/11/27
3194 NGIFAW 020004 F 2002/11/13 NGIFAW 020004 NGIFAW 070024
3194 NGIFAW 020004 F 2002/11/13 NGIFAW 020004 NGIFAW 080024
3194 NGIFAW 020004 F 2002/11/13 NGIFAW 020004 NGIMAW 050019
3194 NGIFAW 020004 F 2002/11/13 NGIFAW 020004 NGIMAW 060014
3194 NGIFAW 020004 F 2002/11/13 NGIFAW 020004 NGIMAW 090017
3956 NGIFAW 030026 F 2003/10/30 NGIFAW 030026 NGIFAW 060022
3956 NGIFAW 030026 F 2003/10/30 NGIFAW 030026 NGIMAW 070029
3956 NGIFAW 030026 F 2003/10/30 NGIFAW 030026 NGIMAW 080029
3948 NGIFAW 030031 F 2003/11/10 NGIFAW 030031 NGIMAW 060029
3948 NGIFAW 030031 F 2003/11/10 NGIFAW 030031 NGIMAW 080032

I'm sure there is an easier way to tackle this, and just cannot see it!
Thanks

Adrian

[Non-text portions of this message have been removed]