Filtered query look up

[FuzzyLogician]

Hey there,

I have two tables, say customers and purchase orders. I am trying to write query that returns a list of customers based on a wild card purchase order search.


(FYI, the query is being used in a VB.NET application)

SELECT Customers.WarehouseID AS Customers_WarehouseID, Customers.Name, PurchaseOrders.PurchaseOrder, PurchaseOrders.WarehouseID AS OBRs_WarehouseID 
FROM Customers INNER JOIN OBRs ON Customers.[WarehouseID] = OBRs.[WarehouseID] 
WHERE (((OBRs.OBR) Like '" & strPOFilter & "*'));

This query almost works how I'd like it to, except in cases where a single customer has multiple purchase orders that begin the same way. So, for example, if customer "mike" has purchase orders "00554" and "00553", if you search for "00", mike will be returned twice. How can I modify the query to only return the customer once?

Is it possible, or should I filter out repeated offenders in my code?

Thanks for your assistance!

[boblarson]

Since you are including data from the Purchase Orders table (including Purchase Order itself), you are stuck as it will bring back all of the purchase orders.

If you just want the unique customers then get rid of the fields from Purchase Orders and then include the Select Distinct at the start.

[FuzzyLogician]

Brilliant, thank you. I did not actually need the purchase order information, just the customer, so it worked perfectly.

Much obliged.