How to select a distinct list where a single reference has 2 sub-references but also has more than 1 of each sub-reference value

Question

I am trying to select a distinct list of TRMANE, TRATSA where there is more than one TRASTA of different values associated to a TRMANE.

Here is an extract from the table; WHTRANP

TRMANE   TRASTA
56819     5   
56819     5   
56819     4   
56833     5   
56833     4   
56844     5   
56844     4   
56880     5   
56880     5   
56880     4   
56880     4   

N.B. - As you can see from the above, it's possible there are multiple TRASTAs of the same value against the TRMANE, I think this is what is causing me issues.

Ideally I'd just end up with;

TRMANE   TRASTA
56819     5   
56819     4   
56833     5   
56833     4   
56844     5   
56844     4   
56880     5   
56880     4   

If I do this;

SELECT TRMANE, TRASTA   
  FROM WHTRANP           
WHERE TRMANE <> 0        
 GROUP BY TRMANE, TRSTA
 HAVING COUNT(TRASTA) > 2
 ORDER BY 1, 2 DESC      

It returns a distinct list of manifests regardless of them having 2 TRASTAS or not - i.e.;

TRMANE   TRASTA
     4     5   
     5     5   
     7     5   
     8     5   
     9     5   
    10     5   
    11     5   
    13     5   
    14     5   
    15     5   
    16     5   

Where as this;

SELECT TRMANE as TRMANE, TRASTA AS TRASTA
  FROM WHTRANP                           
WHERE TRMANE <> 0                        
 GROUP BY TRMANE, trasta                 
 HAVING COUNT(DISTINCT TRASTA) > 2       
 ORDER BY 1, 2 DESC  

Yields no results.

Answer 1

Your query is looking for those rows where the trMane is linked to multiple (unique) traStas. On it's own, this is easy:

SELECT trMane
FROM WHTranP
GROUP BY trMane
HAVING COUNT(DISTINCT traSta) > 1

The problem of course is getting out all those traSta values. The grouping removes our ability to output them (barring a windowing function being available), so we need to do something else: join back to the table again.

SELECT DISTINCT WHTranP.trMane, WHTranP.traSta
FROM WHTranP
JOIN (SELECT trMane
      FROM WHTranP
      GROUP BY trMane
      HAVING COUNT(DISTINCT traSta) > 1) Multiples
  ON Multiples.trMane = WHTranP.trMane
ORDER BY WHTranP.trMane, WHTranP.traSta

SQL Fiddle Example

... by joining back to the list we generated earlier, now all we need to do is remove duplicate traSta values, which the outer DISTINCT handles for us readily.