September 30, 2016 at 4:09 am
Hi,
I've been learning a lot about SQL over the past few months with the help of the site, but am now presented with an issue that I cannot see a solution too - and I am sure its because I'm looking too hard or am not aware of some functionality. I'm sure I could put together a solution with cursors and or loops but really want to avoid this and learn a bit more.
I'm not really looking for anybody to do the work for me, but some pointers in the right direction would be appreciated.
I have a resultset (a bill of materials) and a sequence number where this item appears in the BOM e.g. This is my comparison set
BomItem, Sequence
BOMITEM1, 1
BOMITEM2, 2
BOMITEM3, 3
I also have a resultset which is all other BOMS in the system. I need to find all BOMS in the second set that match the first exactly, based on the item and sequence being the same for all lines e.g In the resultset below, I would return the fact that only BOM1 and BOM3 are exactly the same as my original BOM.
Bom, BomItem, Sequence
BOM1, BOMITEM1, 1
BOM1, BOMITEM2, 2
BOM1, BOMITEM3, 3
BOM2, BOMITEM4, 1
BOM2, BOMITEM5, 2
BOM2, BOMITEM6, 3
BOM3, BOMITEM1, 1
BOM3, BOMITEM2, 2
BOM3, BOMITEM3, 3
BOM4, BOMITEM1, 1
BOM4, BOMITEM2, 2
BOM4, BOMITEM3, 4
My first idea was to create a single string of all BOMITEMS and SEQ to compare row by row, which I have achieved for the original result set, but am sure what to do with the comparison resultset to get it in the same state. I have millions of BOMS to compare to so I'm trying to keep this as resultset orientated as possible.
E.g my original product becomes :-
BOMITEM1-1|BOMITEM2-2|BOMITEM3-3
I would then need to create a second resultset of :-
BOM1, BOMITEM1-1|BOMITEM2-2|BOMITEM3-3
BOM2, BOMITEM4-1|BOMITEM5-2|BOMITEM6-3
BOM3, BOMITEM1-1|BOMITEM2-2|BOMITEM3-3
BOM4, BOMITEM1-1|BOMITEM2-2|BOMITEM3-4
Thanks in advance.
September 30, 2016 at 4:57 am
Some pointers in the right direction - and kudos for asking for them instead of a solution. The type of problem you are investigating is called "Relational Division".
The late Dwain Camps wrote an excellent article over on Simple Talk a couple of years back:
https://www.simple-talk.com/sql/learn-sql-server/high-performance-relational-division-in-sql-server/[/url]
Post back if you have issues - relational division can be tricky to conceptualise.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 30, 2016 at 6:19 am
Hi SSCrazy Eights,
Thank you very much for pointing this out, exactly what I needed. I have been able to solve the problem and learnt so much more along the way.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply