Sprotty Sprotty - 24 days ago 7
SQL Question

SELECT DISTINCT with composite key

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

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
   )