Limit number of associated records 1

[kadochnikov]

I am joining two databases together and pulling “products” from one and “customers” from another. I would like to have all products, but only 3 customers per product? How can I limit these customers?

 

[tlbroadbent]

I don't know the exact structure of your tables nor the relationship between them. However, you should be able to use the following as a starting point.

SELECT p.ProdID, p.Desc, C.CustID, c.CustName
FROM Products As p INNER JOIN Customers As c
ON p.ProdId=C.ProdID
WHERE c.CustID In (Select Top 3 CustID From Customers Where ProdId=p.ProdID);

The correlated sub query selects the 3 records from the customer table for each ProdID. The outer query selects only the customers that are selected by the sub query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[kadochnikov]

Thanks a lot, I realy do not understand how it works, but it worked perfectly!!!