I have a item file with one item per record and a detail file with many customers with many descriptions. Each customer can have many items. Each item can have many customers.
File 1 Item ID ie item 1, item 2 File 2 Item ID Customer Number ie item 1, cust 1, item 2, cust 2, item 1, cust2, item2 cust1
I am trying to get a list of item IDs with only one customer number showing an example of one customer who has each item.
Select * from file1 left join (select distinct itemid, customernumber from File2) B ON A.itemid = b.itemid
Gives me too many results.
item 1, Cust 1 item 2, Cust 2
Or this would be okay too:
item 1, Cust 1 item 2, Cust 1
I am looking for one example customer for each item. Any advice or suggestions would be greatly appreciated.