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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  January 22, 2020 at 2:15 am

  #3717218

  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 )

• as_1234

  SSCrazy

  Points: 2865

  More actions

  January 22, 2020 at 11:35 am

  #3717322

  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 ))