Simple:

select *
from MyTable M1
where M1.LANG_CODE5 = 'FR_fr'
or NOT EXISTS(SELECT * FROM MyTable M2
where M2.CODE_ID = M1.CODE_ID
and M2.LANG_CODE5 = 'FR_fr')

Hopefully you have an index on CODE_ID.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Werner F. Bruhin
Sent: 27. januar 2010 12:07
To: firebird-support@yahoogroups.com
Subject: [firebird-support] select help needed

I have a table which has these rows:

ID CODE_ID LANG_CODE5 NAME SHORT_NAME CREATED_AT UPDATED_AT
3 3 EN_en english name 3 en name 3 26/01/2010 26/01/2010 17:37:28
4 3 FR_fr french name 3 fr name 3 26/01/2010 26/01/2010 17:37:28
5 4 EN_en english name 4 en name 4 26/01/2010 26/01/2010 17:38:16
6 4 FR_fr french name 4 fr name 4 26/01/2010 26/01/2010 17:38:16
7 5 EN_en english name 5 en name 5 26/01/2010 26/01/2010 17:53:37


I can't figure out how to write a select which will give me "ID's" 4, 6,
and 7.

In other words I want all rows with "LANG_CODE5" = 'FR_fr", plus all
with "EN_en" where there is no corresponding "FR_fr".

Is this possible with a select?

Werner