-2

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

ID No Status
32 98765 0
32 0 2
70 43210 0
70 0 2

The final output needs to be:

ID No Status
32 98765 0
70 43210 0

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!

Mr.ED
  • 1
  • 1

3 Answers3

1

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
Uday Dodiya
  • 339
  • 2
  • 15
1

Try Following This, you get exact output

Select ID,No,Status From YOURTABLE Where ID IN (Select ID From YOURTABLE 
Group By ID Having Count(ID) > 1)
And Status = 0 And No <> 0
Liger
  • 21
  • 2
0

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.

ahmed
  • 9,071
  • 3
  • 9
  • 22
  • If there is an ID with multiple No values <> 0 and status = 0, all of them will be returned by this query, i.e. (32,98765,0),(32,98766,0). If you want to select only one of them, you have to specify which one should be selected i.e. MAX(No) or MIN(No) – ahmed Aug 30 '22 at 06:25