none
how to create query based on optional field on temp table ? RRS feed

  • Question

  • 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

    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)

    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 )

    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)
    


    Wednesday, January 22, 2020 1:42 AM

Answers

  • Hi engahmedbarbary,

    Sorry for my poor understanding . Is there anything you need in the following code?

    ------ 1 
    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 )
    and  m.TradeCode is null
    union all
    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 ) and m.TradeCode is null)
    or (not exists ( select 1 from  #TradeCodes t where t.TradeCode=m.TradeCode)) )
    and  m.TradeCode is not null
    /*
    ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
    600         Bicycle                                                                                                                                                                                                  11          NULL
    700         plan                                                                                                                                                                                                     11          NULL
    100         silicon                                                                                                                                                                                                  10          15
    200         motherboard                                                                                                                                                                                              10          15
    300         iron                                                                                                                                                                                                     10          15
    400         plastic                                                                                                                                                                                                  10          15
    */
    
    ------ 2
    if exists (select 1 from #tempsupplier where TradeCode is null)
    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 )
    else 
    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 ) and m.TradeCode is null)
    or (not exists ( select 1 from  #TradeCodes t where t.TradeCode=m.TradeCode) )
    /*
    PartID      PartNumber                                                                                                                                                                                               SupplierId  TradeCode
    ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
    100         silicon                                                                                                                                                                                                  10          15
    200         motherboard                                                                                                                                                                                              10          15
    600         Bicycle                                                                                                                                                                                                  11          NULL
    700         plan                                                                                                                                                                                                     11          NULL
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 22, 2020 7:10 AM

All replies

  • I don't know exactly what you need. Here are some queries:

    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)
     
    
    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 ) and m.TradeCode is null)
    or (not exists ( select 1 from  #TradeCodes t where t.TradeCode=m.TradeCode) and m.TradeCode is not null)
    
    --*****************
    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 )
    and  m.TradeCode is null
    
    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)
     and m.TradeCode is not null 
    
    
    drop table #parts,#TradeCodes,#tempsupplier

    Wednesday, January 22, 2020 2:13 AM
    Moderator
  • Exactly i need after fill 3 temp tables with data to make

    check if trade code on tempsupplier is null then use

    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 )

    if tradecode in tempsupplier is not null the use 

    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 ) and m.TradeCode is null)
    or (not exists ( select 1 from  #TradeCodes t where t.TradeCode=m.TradeCode) 

    my problem how to separate two statement in case i have null column parameter or not

    Wednesday, January 22, 2020 6:22 AM
  • Hi engahmedbarbary,

    Sorry for my poor understanding . Is there anything you need in the following code?

    ------ 1 
    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 )
    and  m.TradeCode is null
    union all
    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 ) and m.TradeCode is null)
    or (not exists ( select 1 from  #TradeCodes t where t.TradeCode=m.TradeCode)) )
    and  m.TradeCode is not null
    /*
    ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
    600         Bicycle                                                                                                                                                                                                  11          NULL
    700         plan                                                                                                                                                                                                     11          NULL
    100         silicon                                                                                                                                                                                                  10          15
    200         motherboard                                                                                                                                                                                              10          15
    300         iron                                                                                                                                                                                                     10          15
    400         plastic                                                                                                                                                                                                  10          15
    */
    
    ------ 2
    if exists (select 1 from #tempsupplier where TradeCode is null)
    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 )
    else 
    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 ) and m.TradeCode is null)
    or (not exists ( select 1 from  #TradeCodes t where t.TradeCode=m.TradeCode) )
    /*
    PartID      PartNumber                                                                                                                                                                                               SupplierId  TradeCode
    ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
    100         silicon                                                                                                                                                                                                  10          15
    200         motherboard                                                                                                                                                                                              10          15
    600         Bicycle                                                                                                                                                                                                  11          NULL
    700         plan                                                                                                                                                                                                     11          NULL
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 22, 2020 7:10 AM