SELECT DISTINCT with composite key
<p>This is a simplification of the problem I have, but using this example I want to get all the product records for a given customer.
Give the customer may have many orders and some orders may be for the same product I don't want to pull back duplicate products. 
The product table also has a composite key on it.</p>

<p>In an ideal world this would be simple and the following Query would do the job. However as the table contains an nText column, SQL Server complains that "The ntext data type cannot be selected as DISTINCT because it is not comparable."</p>

<pre><code>SELECT distinct p.idA, p.idB, p.descriptionNTEXT
FROM p product, o order, c customer
WHERE o.productID = p.idA AND o.subProductID = p.idB AND o.customerID = c.ID
</code></pre>

<p>I would like to re-structure this to something like </p>

<pre><code>SELECT p1.idA, p1.idB, p1.descriptionNTEXT
FROM   p1 product
WHERE (p1.idA, p1.idB) IN
   (
       SELECT p.idA, p.idB
       FROM p product, o order, c customer
       WHERE o.productID = p.idA AND o.subProductID = p.idB AND o.customerID = c.ID
   )
</code></pre>

<p>See <a href="https://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns">How do I (or can I) SELECT DISTINCT on multiple columns?</a> </p>

<p>However this approach does not work on SQL Server</p>

<p>Any Ideas? </p>

<p>Ideally I need a portable solution, something that will at least work on SQLServer, MySQL &amp; Oracle. But DB Specific solutions are better than nothing!</p>

<p>The following data shows roughly what I want.</p>

<pre><code>Customer
ID name
1  Fred

Order
ID  CustomerID productID subProductID
10  1          100       200
11  1          100       200
12  1          100       200
13  1          101       201

Product
IDA  IDB descriptionNTEXT
100  200 'product 1'
101  201 'product 2'    
102  203 'product 3'    

Expected Result
IDA  IDB descriptionNTEXT
100  200 'product 1'
101  201 'product 2'    
</code></pre>