Using a nested select

[D-niss]

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

[si_the_geek]

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

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')
      )

[D-niss]

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

Thanks a bunch!