Post reply

How to get parts when i have same part and same code type repeated with differe?

  • August 7, 2021 at 9:35 am

    Go to Answer

    #3917733

    I work on sql server 2012 i face issue i need to get partid have same code type id

    but have different code and different value

    create table #tradecode
     (
     PartId int,
     codetypeId int,
     code nvarchar(20),
     zvalue nvarchar(20)
     )
     insert into #tradecode(PartID,CodeTypeId,Code,Zvalue)
     values
     (717286,849774,'8532240000','Multilayer'),
     (717286,849774,'8532240022','Multilayer'),
     (717286,849774,'8532240022','Chip'),
        
     (7172899,849777,'8532240033','Multilayer'),
     (7172899,849777,'8532240033','silicon'),
     (7172899,849777,'8532240033','Multilayer')

    expected result

     PartId    codetypeId    code    zvalue
     717286    849774    8532240000    Multilayer
     717286    849774    8532240022    Multilayer
     717286    849774    8532240022    Chip

    so partid 717286 have code type 849774 and have different on code as it have two code

    8532240000 and 8532240022 and different value as chip and Multilayer

    for same code

    because 8532240022 have two values (Multilayer, Chip)

  • August 7, 2021 at 10:04 pm

    #3917823

    This is your 123rd forum topic. Can you write a SELECT query that identifies the parts you want to return - you may want to think about GROUP BY, HAVING, MIN and MAX

  • August 7, 2021 at 10:06 pm

    Answer

    #3917824

    I'm not at all sure I have understood your question correctly, but this query does produce the desired outcome:

     WITH
    	MultiCodes AS (
    		SELECT PartID, CodeTypeId, zValue
    		FROM #tradecode
    		GROUP BY PartID, CodeTypeId, zValue
    		HAVING COUNT(DISTINCT Code) > 1
    	),
    	MultiCodePartIDs AS (
    		SELECT PartID
    		FROM MultiCodes
    		GROUP BY PartId
    	)

    SELECT tc.PartID, tc.CodeTypeId, tc.Code, tc.Zvalue
    FROM #tradecode tc
    INNER JOIN MultiCodePartIDs mc ON mc.PartId=tc.PartId

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply