I need to do some Database sorting and filtering: my DB looks like this:

The final output needs to be:

If an ID with the same value is found (if a singular value is found, it is not displayed), select only the one with status = 0 and no != 0 will be selected and displayed.

Thanks in advance!

CodePudding user response：

Try This

Select ID,No,Status From TableData Where ID IN (Select ID From TableData  
Group By ID Having Count(ID) > 1)
And Status = 0 And No <> 0 

CodePudding user response：

Try the following:

Select A.ID, A.No, A.Status From
tblname A
Join
(
  Select ID, COUNT(*) cn
  From tblname
  Group By ID
  Having COUNT(*)>1
) B
On A.ID = B.ID
Where A.Status = 0 And A.No <> 0

Or using Exists with correlated subquery:

Select A.ID, A.No, A.Status From
tblname A
Where A.Status = 0 And A.No <> 0
And Exists (Select 1 From tblname B Where B.ID = A.ID And A.No <> B.No)

See a demo from db<>fiddle.