October 8, 2010 at 8:52 am
I have an issue that i'm trying to figure out. I have a record set, see example below, that is a list of part numbers and there alternates. I need to build a list out of these part numbers that only show unique part numbers. For example using the data below the record set I want is:
279036-001
404715-001
So it only shows a unique listing cause all the rest are duplicates of these parts. Any help would be appreciated. I could probably do this through a cursor or something and keep looking for uniqueness but i'm trying to find something more efficient.
Thanks
create table [dbo].[MySpares]
(
[Pkey] [int] IDENTITY(1,1) NOT NULL,
[MainPartNumber] [varchar] (300) NOT NULL,
[AltPartNumber] [varchar] (300) Not Null
)
;
insert into dbo.MySpares
(MainPartNumber,AltPartNumber)
values('279036-001','289544-001')
insert into dbo.MySpares
(MainPartNumber,AltPartNumber)
values('289544-001','279036-001')
insert into dbo.MySpares
(MainPartNumber,AltPartNumber)
values('404715-001','411028-001')
insert into dbo.MySpares
(MainPartNumber,AltPartNumber)
values('411028-001','404715-001')
insert into dbo.MySpares
(MainPartNumber,AltPartNumber)
values('411028-001','279036-001')
insert into dbo.MySpares
(MainPartNumber,AltPartNumber)
values('279036-001','411028-001
October 8, 2010 at 11:52 am
I noticed, in your example, you only listed two of the unique numbers instead of all four. Is there a reason why the other two numbers should not come up or did you just tired of typing them?
If the later... Since part numbers repeat between both columns, the easiest way to do this is move everything into a Temp table.
CREATE TABLE dbo.#MyTemp (PartNumber varchar(300) );
INSERT INTO dbo.#MyTemp (PartNumber)
SELECT DISTINCT MainPartNumber
FROM dbo.MySpares;
--Select a Distinct & Unique part number
INSERT INTO dbo.#MyTemp (PartNumber)
SELECT DISTINCT AltPartNumber
FROM dbo.MySpares ms
LEFT OUTER JOIN dbo.#MyTemp mt
ON ms.AltPartNumber = mt.PartNumber
WHERE mt.PartNumber IS NULL;
--Add in the AltPartNumbers where the number doesn't already exist in the temp table
SELECT PartNumber
FROM dbo.#MyTemp;
This prevents you from having to make schema changes and gets you truly unique numbers.
There are probably more elegant ways of doing this, but this is the quick-n-dirty method.
October 8, 2010 at 12:12 pm
First of all thanks for your help, i'm learning alot from this. To answer some questions.
Do you really have a part number 300 character long without any check digits or industry standard? Only used varchar as just an example, we do have part numbers that are very unique and user input.
Do you now about GTIN, UPC, EAN and other standards? No I don't and will have to look that up.
We are very much a self taught shop so all this is a good learn experience, i'm still pretty new at t-sql.
What i'm trying to accomplish is with the record set that I gave, I only want the one of the possible combination of part number. The basic idea is there are part number that have alternates of each other. Out of an order list I only want to order one of the parts not all of its alternates as well.
For example if my order list was
279036-001
289544-001
404715-001
And I check my alternate list and find out this
279036-001 =289544-001
289544-001 = 411028-001
411028-001 = 404715-001
ultimately i only want to order
279036-001 because it relates to everything else.
I'm just trying to find out how i can get that result in sql. Thanks for any help or incite.
October 8, 2010 at 12:23 pm
Yikes. You're trying to eliminate cascading values? That's not nearly as easy as you presented it and changes both answers you've received on this thread.
Leaning towards Celko on this one... Are you in a position to redesign your schema or are you stuck with the one that you have?
EDIT: And how can you possibly know which is the "original" part number? Or the main one which needs to be ordered?
October 8, 2010 at 12:27 pm
Ya its kind of a pain. I can change a few thing, as far as knowing which one to order its really it doesn't really matter as long as its only one.
October 8, 2010 at 12:38 pm
I accidently hit enter on this post.. wish I could have deleted it.
October 8, 2010 at 12:49 pm
Then I suggest doing more of a normalized set up with all the "main" part numbers in one table and all the alternate part numbers in a reference table.
Just an FYI: Celko and I disagree with the use of Identity as a Primary Key. He's against it, I'm for it, especially when you don't have a good number to use as a natural key. Use whichever method works best for you.
USE MyDatabase;
GO
CREATE TABLE dbo.PART (PartID int IDENTITY(1,1) NOT NULL,
PartNum varchar(30),
CONSTRAINT PK_Part_PartID PRIMARY KEY CLUSTERED
( PartID ASC ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY
--Check what padding you want on your index before running this
) ON Primary;
GO
CREATE TABLE dbo.AltPart (AltPartID int IDENTITY(1,1) NOT NULL, PartID int NOT NULL,
AltPartNumber varchar(30),
CONSTRAINT PK_AltPart_AltPartID PRIMARY KEY CLUSTERED
( AltPartID ASC ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY
--Check what padding you want on your index before running this
) ON Primary;
ALTER TABLE dbo.AltPart WITH CHECK ADD CONSTRAINT FK_AltPart_Part FOREIGN KEY(PartID)
REFERENCES dbo.Part (PartID)
GO
INSERT INTO dbo.Part (PartNumber)
SELECT '279036-001';
INSERT INTO dbo.AltPart (PartID, AltPartNumber)
(SELECT PartID, '289544-001'
FROM dbo.Part
WHERE PartNumber = '279036-001'
UNION ALL -- Prevents needing multiple INSERT statements
SELECT PartID, '411028-001'
FROM dbo.Part
WHERE PartNumber = '279036-001'
UNION ALL
SELECT PartID, '404715-001'
FROM dbo.Part
WHERE PartNumber = '279036-001' );
After that, you can take your orders and reference AltPart.
SELECT DISTINCT p.PartID, p.PartNumber
FROM dbo.Part p
JOIN dbo.AltPart ap
ON p.PartID = ap.PartID
WHERE ap.AltPartNumber in ('411028-001', '404715-001','289544-001');
And that should fix it for you.
October 8, 2010 at 2:09 pm
Thank you this helps
October 12, 2010 at 5:39 am
You're welcome. Let us know if you need anything else.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply