Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compare 2 tables... How to do that??? Expand / Collapse
Author
Message
Posted Tuesday, June 4, 2013 3:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
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..
Post #1459631
Posted Tuesday, June 4, 2013 4:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:49 AM
Points: 157, Visits: 285
You just need to replace the .noc out on the join. ie..

from t1
join t2
on t1.cola = replace(t2.cola,'.noc','')
Post #1459641
Posted Tuesday, June 4, 2013 5:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
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..
Post #1459674
Posted Tuesday, June 4, 2013 5:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 12,881, Visits: 31,821
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1459688
Posted Monday, July 1, 2013 1:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
TNX A LOT :) I use it for a lot reports :)
Post #1468955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse