Using a nested select

Hi there fellow coders,

I'm trying to create a nested select stmt but I'm not sure how it goes.

I have a table that goes as follow:

Style_Desc
Style_ID
Color_ID
Match

The match field contain either "Y" or "N".

Then I have a basic stmt as in:

Select * from tbl where....

In my returning recordset, some Style_ID are duplicated because one record may contain a "y" and the other record may contain a "N".

I want to leave out the record that contain a "N" , BUT ONLY, if there is a record, for the same Style_ID that contain a "Y". Otherwise I want to return the one with a "N".

So basically, if I come accross records with the same Style_ID, and if one of those records contains a "N" and another one contains a "Y" in Match, I want to drop the record with the "N" and keep the one with the "Y" instead.

Am I making sense here?

TIA

I'm not sure if this is the most efficient way, but this should work for you:

Code:

---

Select *
from tbl t1
where Match = 'Y'
or    (Match = 'N'
  And Not Exists (
          SELECT Style_ID
          FROM tbl
          WHERE Style_ID = t1.Style_ID
          AND Match = 'Y')
      )

---

Super! It worked. It was missing a ( after the where but I got it to work.

Thanks a bunch!