Compare 2 tables... How to do that???

• GG_BI_GG

  SSCrazy

  Points: 2627

  More actions

  June 4, 2013 at 3:59 am

  #278138

  Hi SQL GURU's,

  Im trying to compare 2 table but i dont want that Table2 use a text .noc in the result.

  Following syntax was just:

  select snetworkname from WhatsUp.dbo.NetworkInterface right outer join WhatsUp.dbo.device on WhatsUp.dbo.NetworkInterface.nNetworkInterfaceID = whatsup.dbo.device.nDefaultNetworkInterfaceID

  where sNetworkName NOT IN (select item_keya as snetwork from dbo.item where item_keya is not null and item_keya <> '')

  I get the following result:

  AA-SR-CO-0007.noc

  abb-beanr-wrh1.noc

  abb-be-s-cmzav.noc

  ABB-BEZAV-WRH10.noc

  ABB-BEZAV-WRH6.noc

  ABB-BEZAV-WRH7.noc

  ABB-BEZAV-WRH8.noc

  ABB-BEZAV-WRH9.noc

  abb-lustn-wrh1.noc

  abb-nlamv-wrh1.noc

  ABB-NLEDE-WRH3.noc

  ABB-NLETT-WRH2.noc

  ABB-NLRTM-WRH1.noc

  ABB-NLRTM-WRH2.noc

  You see that the colums networkname ends always with '.noc'...

  The column item_keya dont have the .noc in his results. What i want is een syntax that gives the results without .noc in it and still compare with the columns. Like this results:

  AA-SR-CO-0007

  abb-beanr-wrh1

  abb-be-s-cmzav

  ABB-BEZAV-WRH10

  ABB-BEZAV-WRH6

  ABB-BEZAV-WRH7

  ABB-BEZAV-WRH8

  ABB-BEZAV-WRH9

  abb-lustn-wrh1

  abb-nlamv-wrh1

  ABB-NLEDE-WRH3

  ABB-NLETT-WRH2

  ABB-NLRTM-WRH1

  ABB-NLRTM-WRH2

  How can i get that result and still compare with the two tables?

  I have just the Like syntax but that not what im looking for because he take only the words..

• Farlzy

  Mr or Mrs. 500

  Points: 561

  More actions

  June 4, 2013 at 4:15 am

  #1620854

  You just need to replace the .noc out on the join. ie..

  from t1

  join t2

  on t1.cola = replace(t2.cola,'.noc','')

• GG_BI_GG

  SSCrazy

  Points: 2627

  More actions

  June 4, 2013 at 5:22 am

  #1620887

  Farlzy (6/4/2013)

  ---

  You just need to replace the .noc out on the join. ie..

  from t1

  join t2

  on t1.cola = replace(t2.cola,'.noc','')

  can you use my SYntax what you mean?

  select snetworkname from WhatsUp.dbo.NetworkInterface right outer join WhatsUp.dbo.device on WhatsUp.dbo.NetworkInterface.nNetworkInterfaceID = whatsup.dbo.device.nDefaultNetworkInterfaceID

  where sNetworkName NOT IN (select item_keya as snetwork from dbo.item where item_keya is not null and item_keya <> '')

  Because i want the 2 tables they must be compare together and only give me values there not in table item_keya...

  Only the table snetworkname have in the value '.noc' so i get an result that says that every device not in my system is.. But is not true because '.noc' is the problem so i want that snetworkname disable the '.noc' and still have the value..

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 4, 2013 at 5:54 am

  #1620901

  like this?

  SELECT

  snetworkname,

  REPLACE(sNetworkName,'.noc','')

  FROM WhatsUp.dbo.NetworkInterface

  RIGHT OUTER JOIN WhatsUp.dbo.device

  ON WhatsUp.dbo.NetworkInterface.nNetworkInterfaceID = whatsup.dbo.device.nDefaultNetworkInterfaceID

  WHERE REPLACE(sNetworkName,'.noc','') NOT IN (SELECT

  REPLACE(item_keya,'.noc','') AS snetwork

  FROM dbo.item

  WHERE item_keya IS NOT NULL

  AND item_keya <> '')

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• GG_BI_GG

  SSCrazy

  Points: 2627

  More actions

  July 1, 2013 at 1:26 am

  #1628622

  TNX A LOT 🙂 I use it for a lot reports 🙂