Find records that comply with either or criteria

[DonWolfi]

I am trying to get of our the database of our finance system all records where the the first field is the same and that either comply with condition a or condition b.

Example:

[HTML]
/<font face='Courier'
>field 1 field 2 field 3 field 4<
>179000 990001 0 100.00<
>179500 100001 TAB123 123.00<
>180000 100002 TAB250 250.00<
>180000 990002 0 1000.00<
/font>
[/HTML]
The conditions for the query are:
condition a: where field 3 LIKE 'TAB%'
condition b: where field 2 LIKE '99%'

The result I wish to see is:

[HTML]
/<font face='Courier'
>field 1 field 2 field 3 field 4<
>180000 100002 TAB250 250.00<
>180000 990002 0 1000.00<
/font>
[/HTML]

because field 1 is the same for both records and either condition a or condition b are met. The first and second records disqualify, because there is no second record with an equal value in field 1 and that matches the other condition.

I would be very obliged if someone could help me with the issue. I think I spent to much time thinking around it!

Many Txs
Wolfgang

[iburyak]

1.

select * from table_name a
where ([field 3] like 'TAB%' and exists (select * from table_name where [field 1] = a.[field 1] and [field 2] like '99%'))
or ([field 2] like '99%' and exists (select * from table_name where [field 1] = a.[field 1] and [field 3] like 'TAB%'))


2.
select * from table_name a
where [field 1] in (select [field 1]
from table_name
where [field 3] like 'TAB%' or [field 2] like '99%'
group by [field 1]
having count(*) = 2)