Master in SQL please Help

[irshu]

Any master in SQL Queries Help: Complicated query I have a table, as similar to this ID Status Deleted 1 Repeat y 2 Repeat y 3 Repeat N 4 Repeat N 5 sUBMIT y 6 sUBMIT y 7 sUBMIT N 8 sUBMIT N 9 Pending Y . . . . from the table like this i have to retrive distinct records where delted = N eg: in the above table after executing query result should be ID Status Deleted 3 Repeat N 7 SUBMIT N 9 Pending Y

[Xiangyang Liu]

irshu wrote: from the table like this i have to retrive distinct records where delted = N eg: in the above table after executing query result should be ID Status Deleted 3 Repeat N 7 SUBMIT N 9 Pending Y The query does not seem to be complicated, but it is not clear what you want. You said "deleted = N" and your result set has a record with "deleted = Y". Please explain.[

My articles and software tools

](http://mysite.verizon.net/XiangYangL/index.htm)

[irshu]

say if i have records with same status field and with deleted ='N' and delted = 'Y' then i should select one record with deleted = 'N' and i should not select deleted = 'Y' and say if i have records with the same status and with deleted ='y' and No records with delted='N' with the same status then i should select delted ='Y' and one more thing in this is, ID is the primary Key

[Xiangyang Liu]

Wow, that is complicated, but not impossible. Here is the SQL statement that will get what you want (using union and nested query): SELECT min(ID), Status, 'N' FROM myTable WHERE Status in (select distinct Status from myTable where Deleted = 'N') GROUP BY Status UNION SELECT min(ID), Status, 'Y' FROM myTable WHERE Status in (select distinct Status from myTable where not Status in (select distinct Status from myTable where Deleted = 'N')) GROUP BY Status;[

My articles and software tools

](http://mysite.verizon.net/XiangYangL/index.htm)

[Xiangyang Liu]

Oops, the following is simpler: SELECT min(ID), Status, 'N' FROM myTable WHERE Status in (select distinct Status from myTable where Deleted = 'N') GROUP BY Status UNION SELECT min(ID), Status, 'Y' FROM myTable WHERE not Status in (select distinct Status from myTable where Deleted = 'N') GROUP BY Status;[

My articles and software tools

](http://mysite.verizon.net/XiangYangL/index.htm)

[Anonymous]

Thank you very much for helping in this regard. with regards irsh

[irshu]

Thank you very much for helping in this regard. with regards irsh

[irshu]

Thank you very much for helping in this regard. with regards irsh