SELECT DISTINCT with composite key

Question

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.

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."

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

I would like to re-structure this to something like

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
   )

See How do I (or can I) SELECT DISTINCT on multiple columns?

However this approach does not work on SQL Server

Any Ideas?

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

The following data shows roughly what I want.

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'    

Answer 1

Cast NTEXT to NVARCHAR(MAX)

SELECT distinct p.idA, p.idB, cast (p.descriptionNTEXT as nvarchar(max))
FROM p product, o order, c customer
WHERE o.productID = p.idA AND o.subProductID = p.idB AND o.customerID = c.ID

---

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

https://msdn.microsoft.com/en-us/library/ms187993.aspx

---

nvarchar [ ( n | max ) ] Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

https://msdn.microsoft.com/en-us/library/ms186939.aspx

Answer 2

looking at your code, you seem to want to use IN over multiple fields - this can be achieved via EXISTS - it asks if the specified query returns any row - so based on your query it might be something like

SELECT p1.idA, p1.idB, p1.descriptionNTEXT
FROM   p1 product
WHERE EXISTS
   (
       SELECT 1
       FROM p product, o order, c customer
       WHERE o.productID = p.idA AND o.subProductID = p.idB AND o.customerID = c.ID 
             AND p1.idA = p.ida 
             AND p1.idB = p.idb
   )

Answer 3

First do distinct, then join to products.

select p.*
    from (
        select distinct CustomerID, productID, subProductID
        from [Order]
    ) do
    join Product p on p.productID = do.IDA and p.subProductID = do.IDB

Answer 4

You can try this without converting it to another type

SELECT p1.idA, p1.idB, p1.descriptionNTEXT
from product p1 where p1.IDA in( SELECT Distinct p.idA FROM  product p,order o,customer c WHERE o.productID = p.idA AND o.subProductID = p.idB AND o.customerID = c.ID)