-1

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.

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

Improve this question
5
  • 2
    Can you show sample data and how you want the output to look (like, if a file has multiple items or customers).
    – Aaron Bertrand
    Jan 13, 2018 at 19:25
  • I am so sick of the haters on SO that downtick you because they don't understand the problem instead of asking for clarification like Aaron. I fleshed out my question. I hope you can help.
    – Missy
    Jan 14, 2018 at 14:25
  • 1
    Your question is confusing to me - "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. " - do you want a list of items that only have one customer (because your sample output does not agree with that) OR do you want a list customers that have each item (your sample output also does not agree with that). I think more sample data (as well as desired output) is needed along with additional explanation of why you got the desired output.
    – Scott Hodgin - Retired
    Jan 14, 2018 at 15:13
  • 1
    For example: using your desired output, why do you have a row for item1/cust1 when cust1 also has item2? Likewise, why do you have a row for item2/cust2 when cust2 also has item1?
    – Scott Hodgin - Retired
    Jan 14, 2018 at 15:20
  • Thank you @ScottHodgin -- I am looking for an example customer for each item. I might have 10 customers that bought item 1 but I want to capture only one of those 10 customers as a sample of a person who bought Item 1.
    – Missy
    Jan 14, 2018 at 15:47

2 Answers 2

Reset to default
3

For future questions, please post the definition of the tables and some sample data, like this:

CREATE TABLE File1 (ItemID VARCHAR(10) PRIMARY KEY)

CREATE TABLE File2 (
    PK INT IDENTITY PRIMARY KEY,
    ItemID VARCHAR(10) NOT NULL FOREIGN KEY REFERENCES dbo.File1,
    CustomerNumber VARCHAR(20) NOT NULL,
    UNIQUE (ItemID, CustomerNumber)
)

INSERT INTO dbo.File1 (ItemID) VALUES ('Item 1')
INSERT INTO dbo.File1 (ItemID) VALUES ('Item 2')

INSERT INTO dbo.File2 (ItemID, CustomerNumber) VALUES ('Item 1', 'Cust 1')
INSERT INTO dbo.File2 (ItemID, CustomerNumber) VALUES ('Item 2', 'Cust 2')
INSERT INTO dbo.File2 (ItemID, CustomerNumber) VALUES ('Item 1', 'Cust 2')
INSERT INTO dbo.File2 (ItemID, CustomerNumber) VALUES ('Item 2', 'Cust 1')

The following queries provide the results you specified:

SELECT ItemID, MIN(CustomerNumber) AS CustomerNumber FROM dbo.File2 GROUP BY ItemID

SELECT x.ItemID, x.CustomerNumber FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY PK) AS RowNum 
    FROM dbo.File2
) x WHERE x.RowNum=1

The first query returns the minimum CustomerNumber for each item.

The second query returns the first CustomerNumber for each item, provided they were inserted in a specific order (in this case, this order is stored in an identity column named PK).

Improve this answer
1
  • Thanks -- I'll try it out. I would have never gotten there without you.
    – Missy
    Jan 14, 2018 at 16:22
1

If you truly don't care which customer to pick, this will work:

Select * from file1 left join         
  (select itemid, min(customernumber) from File2 group by itemid) B 
ON A.itemid = b.itemid
Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.