SQL for finding duplicate records

[Andrew Lindsay]

I have two tables: acct_badge_lookup and account_badge

I want to pull two fields from acct_badge_lookup: acct_badge_id and account_id

I want to pull three fields from account_badge: account_badge_id, badge_id, badge_status_id

I want to return only the records where the acct_badge_id = account_badge_id, but only when it appears in more than one account_id, and the badge_status_id equals 1 or 2.

I need just the pure SQL. Any help would be appreciated. I've looked at Unions, sub-queries, and joins, but I'm don't know how to accomplish what I'm needing.

Thanks in advance,
Andrew

 

[spacebar]

-- Something like this should do it for you:

-- 1st determine the ones in account_badge that have > 1 unique account_id's
With ab_list as (
  select account_badge_id, count( distinct account_id ) as badge_id_acct_count
   from  account_badge
  where  badge_status_id in( 1, 2 )
  group  by account_badge_id
  having count( distinct account_id ) > 1 )
-- then just select the data joining to the "subquery_factoring_clause" created with an
-- inner join so that only the rows you want are selected
select abk.acct_badge_id, abk.account_id,
       ab.account_badge_id, ab.badge_id, ab.badge_status_id
 from  acct_badge_lookup abk join account_badge ab  on abk.acct_badge_id  =  ab.account_badge_id
                             join ab_list       abl on abk.acct_badge_id  =  abl.account_badge_id

hth

 

[Andrew Lindsay]

Thank you spacebar, this is exactly what I needed.

I had most the main query figured out, struggling most with the "having count (distinct account_id) > 1". I was putting "having count (*) > 1", which I see now why it didn't work.

For the rest, I didn't have a clue.

Thanks again. I learned a lot here.

Andrew