Please help me this query

All Replies

• Friday, January 18, 2013 6:25 PM

   

   
  

  0
  Try adding 'set ansi_nulls on' in the beginning of your query.
  • Reply
  • Quote

   
• Friday, January 18, 2013 8:03 PM

   

   
  

  0

  You are referencing the same table/view 3 times - effectively self-joining.  The logic seems odd.  Are you certain that the data is so inconsistent to require this approach? 

  In any event, you can use "not exists" instead of "not in" to allow for nulls: 

  select ...
  from v_Add_Remove_Programs as mst
  where not exists (select * from v_Add_Remove_Programs as pub where (pub.Publisher0 like '%SBG%' or pub.Publisher0 like '%Microsoft%')
  and pub.Publisher0 = mst.Publisher0) ...

  Or simply allow for a NULL publisher in your existing query:

  ) and (arp.Publisher0 is null or arp.Publisher0 not in (...))

  • Reply
  • Quote

   
• Friday, January 18, 2013 8:27 PM

   

   
  

  1

  Not sure whether you are looking like this or not. If you could post sample output, that will be helpful.

  SELECT * 
  FROM v_Add_Remove_Programs
  	WHERE DisplayName LIKE '%Bonjour%' 
  		OR DisplayName LIKE '%ACCA paper%' 
  		OR DisplayName LIKE '%AMS%'
  		AND (Publisher IS NULL) 
  				OR (Publisher IN ('Microsoft','SBG'))

  
  

  ---

  Narsimha

  • Reply
  • Quote

   
• Saturday, January 19, 2013 1:29 PM

   

   
  

  0

  Thanks everyone for help. I tried NOT EXISTS AND set asci_nulls Separately.

  In my output I dont want row of microsoft as publisher and ACCA Paper and AMS as displayname. But ACDee i want. cause it does not match either microsoft as pulisher or 'ACCA paper' and 'AMS' as displayname

  Expected Output

  DisplayName            Publisher
  ASK Toolbar              ASK   
  ACDee

   

  Original Table

  DisplayName               Publisher
  AMS
  ASK Toolbar                 ASK  
  AllWindowsPatches    Microsoft
  ACDee
  ACCA paper

  • Edited by raofu09 Saturday, January 19, 2013 3:56 PM correction
  •  
  • Reply
  • Quote

   
• Monday, January 21, 2013 6:00 PM

   

   
  

  0

  Use coalesce for the Publisher.

  Here is a sample code:

  declare @software table (
  displayname varchar(50),
  publisher varchar(50)
  )
  
  insert into @software select 'AMS', NULL
  insert into @software select 'ASK Toolbar', 'ASK'
  insert into @software select 'AllWindowsPatches', 'Microsoft'
  insert into @software select 'ACDee', NULL
  insert into @software select 'ACCA paper 1', NULL
  insert into @software select 'Bonjour', NULL
  insert into @software select 'Attendence Recording System', 'SBG'
  
  select * from @software where displayname not like '%Bonjour%' and displayname not like '%ACCA paper 1%' and displayname not like '%AMS%' and coalesce(publisher,'') not like '%Microsoft%' and coalesce(publisher,'') not like 'SBG'
  

  • Reply
  • Quote

   
• Monday, January 21, 2013 6:34 PM

   

   
  

  0

  declare @software table (
  displayname varchar(50),
  publisher varchar(50)
  )
  insert into @software select 'AMS', NULL
  insert into @software select 'ASK Toolbar', 'ASK'
  insert into @software select 'AllWindowsPatches', 'Microsoft'
  insert into @software select 'ACDee', NULL
  insert into @software select 'ACCA paper 1', NULL
  insert into @software select 'Attendence Recording System', 'SBG'
  SELECT * FROM @software;
  SELECT *
  FROM
  (	
  	SELECT displayname,publisher
  	FROM @software
  	WHERE (publisher NOT LIKE '%Microsoft%' AND publisher NOT LIKE '%SBG%') OR (publisher IS NULL)
  ) AS a
  WHERE (a.displayname NOT LIKE '%AMS%') AND (a.displayname NOT LIKE '%ACCA paper 1%') 

  
  

  ---

  Narsimha

  • Marked As Answer by Iric WenModerator Monday, January 28, 2013 5:58 AM
  •  
  • Reply
  • Quote

   
• Monday, January 21, 2013 7:09 PM

   

   
  

  0

  SELECT *FROM
  (SELECT displayname,publisher 	FROM @software
  	WHERE (publisher NOT LIKE '%Microsoft%' AND publisher NOT LIKE '%SBG%') OR (publisher IS NULL)) AS a
  WHERE (a.displayname NOT LIKE '%AMS%') AND (a.displayname NOT LIKE '%ACCA paper 1%') 

  
  
  • Reply
  • Quote