how to create query based on optional field on temp table ?

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2675

    problem

    how to create query based on optional field on temp table ?

    I work on sql server 2012

    I have temp table #tempsupplier have two fields

    SupplierId mandatory have value

    TradeCode optinal have value may be have or may be not have value

    so i need

    details data as below

    create table #tempsupplier
    (
    SupplierId int,
    TradeCode int
    )
    insert into #tempsupplier(SupplierId,TradeCode)
    values
    (10,15),
    (11,null)

    create table #parts
    (
    PartID int,
    PartNumber nvarchar(200),
    SupplierId int,
    TradeCode int
    )

    insert into #parts
    (PartID,PartNumber,SupplierId)
    values
    (100,'silicon',10),
    (200,'motherboard',10),
    (300,'iron',10),
    (400,'plastic',10),
    (500,'Car',11),
    (600,'Bicycle',11),
    (700,'plan',11)


    create table #TradeCodes
    (
    PartID int,
    TradeCode int
    )
    insert into #TradeCodes
    (PartID,TradeCode)
    values
    (300,10),
    (400,10),
    (500,20)
    if(tradecode have value then execute)

    do specific select

    select p.PartID, p.PartNumber,m.SupplierId,m.TradeCode from #tempsupplier m
    inner join #parts p on p.SupplierId=m.SupplierId
    where not exists ( select 1 from #TradeCodes t where t.TradeCode=m.TradeCode)

    if (tradecode not have value meaning is null then execute depend on supplier only)

    select p.PartID, p.PartNumber,m.SupplierId,m.TradeCode from #tempsupplier m
    inner join #parts p on p.SupplierId=m.SupplierId
    where not exists ( select 1 from #TradeCodes t where t.PartID=p.PartID )
  • as1981

    SSCrazy

    Points: 2617

    I think this might give you the result you need?:

    select p.PartID, p.PartNumber,m.SupplierId,m.TradeCode from #tempsupplier m 
    inner join #parts p on p.SupplierId=m.SupplierId
    where (m.tradecode is not null and not exists ( select 1 from #TradeCodes t where t.TradeCode=m.TradeCode) )
    or (m.TradeCode is null and exists ( select 1 from #TradeCodes t where t.PartID=p.PartID ))

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

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