Query to ignore common records

Question

Post reply

Query to ignore common records

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
February 6, 2010 at 5:10 pm

#229678

I am looking T-SQL to have only different records display/exposed , requirement is that i have one source sourceA where it has servername and IPs and another source SourceB has same servername but may be different IPs so i need to expose those.
Here is the sample tables and sample data
CREATE TABLE [dbo].[SourceA](
[ServerName] [varchar](50) NULL,
127.0.0.1 [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SourceB](
[ServerName] [varchar](50) NULL,
127.0.0.1 [varchar](50) NULL
) ON [PRIMARY]
Sample Data for Source A
ServerName IP
ACC10.0.0.1
ACC10.0.0.3
ABC192.168.1.1
ABC10.0.0.1
ABD10.0.9.3
Sample Data for Source B
ServerNameIP
ACC10.0.0.1
ACC10.0.0.2
ABC192.168.1.1
ACC192.1.1.2
ABD10.0.9.4
and result should expose only the differences between two sources like below based on above data
sourceA.ServerName sourceA.ServerIP SourceB.ServerName SourceB.ServerIP
ACC 10.0.0.3 ACC None
ABC None ABC 192.168.1.1
ABD 10.0.9.3 ABD 10.0.9.4
Thank you!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

select * from dbo.sourcea except select * from dbo.sourceb

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 2

Thanks , i am looking it to display in same report so result set should show head to head comparison as shown below ,

ACC has extra server IP for source A along with common IPs so it display only extra IP for source A and "None" for source B

ABD has different IPs for source A and different IP for source B so it shows head to head...

Here the output looks like ,how i can achieve that?

below resultset based on the sample data posted in original post

SourceA.ServerName SourceA.IP SourceB.ServerName SourceB.IP

ACC10.0.0.3ACC None

ABCNoneABC 192.168.1.1

ABD10.0.9.3ABD 10.0.9.4

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Use a FULL OUTER JOIN. If you don't know how to use it, now's the time to get inline with Books Online. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 4

You might want to fix the sample output data too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 5

Thanks ,I got it , the last ask is now i have sourceA with servername ABC and 5 IPs and SourceB with servername ABC with 3 different IP than source A

but when i join them with sourceA.servername=sourceB.servername it gives me cartesian product and return me 15 rows as IPs are different in both the sources , how i can avoid cartesian product and just list what they have , so i can expose in a report rather making ugly report.

Thanks for your time...

WayneS SSC Guru Points: 95460 More actions · Answer 6

sounds like you need something like:

DECLARE @SourceA TABLE (

[ServerName] [varchar](50) NULL,

127.0.0.1 [varchar](50) NULL

)

DECLARE @SourceB TABLE (

[ServerName] [varchar](50) NULL,

127.0.0.1 [varchar](50) NULL

)

-- insert the sample data

-- notice how it's in a format that makes it easier for us volunteers to copy/paste and use it!

INSERT INTO @SourceA

SELECT 'ACC','10.0.0.1' UNION ALL

SELECT 'ACC','10.0.0.3' UNION ALL

SELECT 'ABC','192.168.1.1' UNION ALL

SELECT 'ABC','10.0.0.1' UNION ALL

SELECT 'ABD','10.0.9.3'

INSERT INTO @SourceB

SELECT 'ACC','10.0.0.1' UNION ALL

SELECT 'ACC','10.0.0.2' UNION ALL

SELECT 'ABC','192.168.1.1' UNION ALL

SELECT 'ACC','192.1.1.2' UNION ALL

SELECT 'ABD','10.0.9.4'

;WITH CTE_A AS

(

-- get the rows in A not in B

select * from @SourceA

except

select * from @SourceB

), CTE_B AS

(

-- get the rows in B not in A

select * from @SourceB

except

select * from @SourceA

), CTE_C AS

(

-- combine the two results together

-- since A can have rows not in B, and B can have rows not in A, need to union these together

-- for each servername, give a number from 1 to 2

select ServerNameA = CTE_A.ServerName,

IPA = CTE_A.IP,

ServerNameB = Coalesce(CTE_B.ServerName, CTE_A.ServerName),

IPB = CTE_B.IP,

TileNbr = NTILE(2) OVER (PARTITION BY CTE_A.ServerName ORDER BY CTE_A.IP)

from CTE_A

LEFT OUTER JOIN CTE_B

ON CTE_B.ServerName = CTE_A.ServerName

AND CTE_B.IP = CTE_A.IP

UNION

select ServerNameA = COALESCE(CTE_A.ServerName, CTE_B.ServerName),

IPA = CTE_A.IP,

ServerNameB = CTE_B.ServerName,

IPB = CTE_B.IP,

TileNbr = NTILE(2) OVER (PARTITION BY CTE_B.ServerName ORDER BY CTE_B.IP)

from CTE_B

LEFT OUTER JOIN CTE_A

ON CTE_B.ServerName = CTE_A.ServerName

AND CTE_B.IP = CTE_A.IP

), CTE_D AS

(

-- assign a row number by servername.

select * ,

RowNbr = ROW_NUMBER() OVER (PARTITION BY ServerNameA ORDER BY ServerNameA, TileNbr)

from CTE_C

)

-- finally, get the results

select ServerName = A.ServerNameA, IPA = COALESCE(A.IPA, B.IPA, 'None'), IPB = COALESCE(A.IPB, B.IPB, 'None')

from CTE_D A

LEFT OUTER JOIN CTE_D B

ON A.ServerNameA = B.ServerNameA

AND A.TileNbr = B.TileNbr

AND A.RowNbr <> B.RowNbr

where A.RowNbr % 2 = 1

This returns:

ServerName IPAIPB

ABC10.0.0.1None

ABD10.0.9.310.0.9.4

ACC10.0.0.310.0.0.2

ACCNone192.1.1.2

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

irobertson Hall of Fame Points: 3695 More actions · Answer 7

Hi,

WayneS - think your solution is missing ABC's duplication?

how about using a pivot to truly get one row per server (using WayneS's test data):

;with cte as (

select * from @SourceA

union

select * from @SourceB

)

, cte2 as (

select *

, dense_rank() over (partition by ServerName order by IP) as IPNumber

from cte

)

select ServerName

, [1] as IP_1

, [2] as IP_2

, [3] as IP_3

, [4] as IP_4

from (

select ServerName, IP, IPNumber

from cte2

) t

pivot (

min(IP) for IPNumber in ([1],[2],[3],[4])

) as pvt

Edit: obvoiusly you would need to know the maximum number of instances of a unique IP/Server combo and adjust the pivot query accordingly...

Regards, Iain

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 8

Thanks the issue is ,i need to get comparison in two data sources with same servername and different IPs but pivot will not show the source of IP.

irobertson Hall of Fame Points: 3695 More actions · Answer 9

It will if you add a flag for the source:

;with cte as (

select *, 'A' as IPSource from @SourceA

union

select *, 'B' from @SourceB

)

, cte2 as (

select *

, dense_rank() over (partition by ServerName, IPSource order by IP) as IPNumber

from cte

)

select ServerName

, IPSource

, [1] as IP_1

, [2] as IP_2

, [3] as IP_3

, [4] as IP_4

from (

select ServerName, IPSource, IP, IPNumber

from cte2

) t

pivot (

min(IP) for IPNumber in ([1],[2],[3],[4])

) as pvt

order by ServerName, IPSource

/*

Output:

ServerName IPSource IP_1 IP_2 IP_3 IP_4

---------- -------- ----------- ----------- ----------- -----------

ABC A 10.0.0.1 192.168.1.1 NULL NULL

ABC B 192.168.1.1 NULL NULL NULL

ABD A 10.0.9.3 NULL NULL NULL

ABD B 10.0.9.4 NULL NULL NULL

ACC A 10.0.0.1 10.0.0.3 NULL NULL

ACC B 10.0.0.1 10.0.0.2 192.1.1.2 NULL

*/

Does this work or do you need a single row per server also?