How to write SQL Query, for avoiding duplicates?

Question

ID     Balance      Account Type      Date
1245    100           HR              01-01-15
1245    500           HC              02-01-15
1325    200           HC              03-01-15
1789    400           HC              04-01-15

In this case, I want only rows With account type as HC and Balance not equal to zero

select * from ABC where Account Type = 'HC'
and Balance<>0;

is not working.

I want only ID which has account type as HC and Balance > 0 and for that ID it should not have Account type as HR.

This query is running on MySql or Sql Server?

Joe Taras
– Joe Taras

2015-02-18 18:06:49 +00:00
Commented Feb 18, 2015 at 18:06 — Joe Taras
– Joe Taras, Commented Feb 18, 2015 at 18:06

Joe Taras · Accepted Answer · 2015-02-18 18:07:21Z

1

Try this:

select * from ABC
where Account_Type = 'HC'
and Balance<>0
and not exists(
    select 'HR'
    FROM abc a2
    where a2.account_type = 'HR'
   AND a2.id = abc.id
)

answered Feb 18, 2015 at 18:07

Joe Taras

15.4k7 gold badges45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Z · Accepted Answer · 2015-02-18 18:07:50Z

0

This should work:

select * 
from ABC A1 
where 
  Account Type = 'HC' and 
  Balance<>0 and 
  not exists (
    select 1 
    from ABC A2 
    where 
      A1.ID = A2.ID And 
      A2.Type = 'HR'
  )

answered Feb 18, 2015 at 18:07

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Collectives™ on Stack Overflow

How to write SQL Query, for avoiding duplicates?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related