Let's assume we have a table T with the following columns:

dc1  dc2  fc1  fc2  lc2
 1    1    11   12   21
 1    1    11   12   22
 1    1    11   12   23
 2    2    22   22   31
 3    3    33   35   32
 3    3    33   37   32

I want a SQL query that returns all the rows that satsfy the following criteria: dc1, dc2 has multiple distinct fc1, lc2 combinations (count > 1). For the above sample data, the SQL would return:

dc1  dc2  fc1  fc2  lc2
 1    1    11   12   21
 1    1    11   12   22
 1    1    11   12   23

    Requires Free Membership to View

The solution is straightforward and utilizes COUNT DISTINCT:

select T.* 
  from T
inner
  join (
       select dc1
            , dc2
         from T            
       group
           by dc1
            , dc2
       having count( distinct fc1,lc2 ) > 1
       ) as multiples
    on T.dc1 = multiples.dc1       
   and T.dc2 = multiples.dc2

Unfortunately, not every database system supports COUNT DISTINCT.

For those that don't, you can obtain the correct results, perhaps not as efficiently, with concatenation or some other expression to combine the two column values. For example, using Microsoft SQL Server syntax --

select T.* 
  from T
inner
  join (
       select dc1
            , dc2
         from T            
       group
           by dc1
            , dc2
       having count( distinct 
                   cast(fc1 as varchar)
                  +cast(lc2 as varchar) ) > 1
       ) as multiples
    on T.dc1 = multiples.dc1       
   and T.dc2 = multiples.dc2

This was first published in June 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: