0

So I've got a table that is used largely for inventory purposes. There's a location, part number, length (a single part can have multiple lengths), user, etc..

How the system is supposed to work is one person scans the parts and lengths, once it's done a second and third person come and scan the parts in succession.

I'm trying to create a list of locations in which no one part/length combination in any location has got multiple scans. So if any part/length combination has been scanned more than once than that entire location is thrown out and not in the final list.

I've been racking my brain and this seems like a simple query but I can't seem to find something that works.

Sevdarkseed
  • 89
  • 1
  • 1
  • 6
  • Did you consider to prevent duplicates using unique index? – Nikolaus Sep 30 '17 at 22:54
  • What is your best not working attempt? – Nikolaus Sep 30 '17 at 22:56
  • The table already has a unique index, so it's an issue of finding duplicates within a subset of that. I was thinking of something like this: SELECT (SELECT COUNT(PartNumber) FROM Table WHERE Location = @Location) as rowsnum, (SELECT DISTINCT COUNT(PartsNumber) FROM table WHERE Location = @Location) as parts, Location FROM table WHERE rownum/parts = 1 Unfortunately this doesn't cover the issue that I'd need to include Partnumber/Length to be sure that it's unique since a part number can have multiple lengths in a single location. – Sevdarkseed Oct 01 '17 at 11:54
  • And can't you extend the unique index to the combination of partnumber&length? – Nikolaus Oct 01 '17 at 14:14
  • Maybe this can help you: [distinct for multiple columns ](https://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns) – Nikolaus Oct 01 '17 at 14:21

0 Answers0