Post reply

How to get partid that have two category and exist on 2000,2200 ?

  • March 15, 2021 at 8:42 pm

    #3858175

    How to get partid that have two category for same part and exist on 2000,2200 ?

    I work on sql server 2012 i face issue i can't get partid that have two category for same part

    and these two category must be on category 2000 and 2200

    as partid 1246 it have two category for same part id

    and also thesetwo category 2000 and 2200

    so How to make select query do that please ?

    create table #category
    (
    PartId int,
    Category int
    )
    insert into #category(PartId,Category)
    values
    (1246,2000),
    (1246,2200),
    (1250,2000),
    (1250,2200),
    (1290,2000),
    (1350,2200),
    (4000,3000),
    (4000,5000)


    what i try :

     select partid,Category from #category
     where category in (2000,2200)
     group by partid
     having count(partid)=2

    expected result

    PartId Category
    1246 2000
    1246 2200
    1250 2000
    1250 2200

    77917-image.png

  • March 15, 2021 at 9:22 pm

    #3858186

    It seems safest to COUNT(DISTINCT CATEGORY) with GROUP BY PartId.  It's not clear if it's necessary to join back to the table to SELECT other CategoryIds.  If other CategoryIds (than 2000 and 2200) are not needed then you could just CROSS JOIN the two rows.

    with p_cte as (
         select partid
         from #category
         where category in(2000,2200)
         group by partid
         having count(distinct Category)=2)
    select p.partId, v.Category
    from p_cte p
         cross join (values (2000),(2200)) v(Category)
    order by p.partId;
    partIdCategory
    12462000
    12462200
    12502000
    12502200

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

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

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