Query to compare two views and find non-matching record ??

Question

Query to compare two views and find non-matching record ??

rk1980factor

SSCommitted

Points: 1681
More actions
May 11, 2016 at 1:38 pm

#307547

I have attached the word document which will show proper views(columns). Please check the attach word document.
There are two Views I have created:
View 1: Employee
Emp_no.Class_no#Org_CodeOrg_Num
4839144 820384323
4323244 332345234
3456544 254345443
534540 235364343
542330 876552938
View 2: Class
Emp_no.Class_no#School$School#AreaCityOrg_CodeOrg_NumState
48391 44 $4500 A24404Liehs820384423 GA
43232 44 $2000 K83 643Kiore332345234 PA
23433 44 $3500 L53 201Crosie254345443 NY
53454 0 $5000 S25924Byline2353 64343 CA
23454 0 $8000 I53 507Train8765 52938 FL
54233 65 $2000 K83 643Kiore5634 52938 PA
These two views have all different column except few common column names. Class table has more column than Employee. Both views does not have same number of columns.
I am looking to write the query which will find me the NON-Matching records from both tables.
Condition of comparison should be combination of 3 columns (EmpNo + Org.Code + Org Num)
So the condition will be compare combination of this three column in one view with combination of three column with another view and show me which are not matching records. With regards to display I just want to display Emp.No column (doesn’t matter if I can’t display any other column).
So basically I want to see all the employee numbers (Emp_no.) which are in View 1:Employee but not in View2: Class and also I want to see all employee numbers (Emp_no.) which are in View 2-Class but not in View1:Employee. BASED on checking 3 columns (Emp.no, Org_Code and Org_Num)
So for example in above case the result should be:
Emp.no
48391 (Org,Num column is not matching)
34565 (Emp.No in View 1 but not in View 2)
23433 (Emp.No in View 2 but not in View 1)
23454 (Emp.No in View 2 but not in View 1)
54233 (Org_Code does not match)
Please let me know if I need to clarify more
Thanks for the help

Viewing 15 posts - 1 through 15 (of 32 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Standard queries will suffice here. Join the two columns on key field and add a where a <> a or b <> b or c <> c. Do make sure you handle NULLs if they are present!!

Next up is a pair of NOT EXISTS (select a where not exists b and vice versa).

BTW, this sounds a lot like a homework assignment ... 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

rk1980factor SSCommitted Points: 1681 More actions · Answer 2

sorry its not homework, just trying to solve complicated problem:

this was the query i created but i don't think it is giving me correct output:

Select a.[Emp_no],b.[Emp_no] FROM [dbo].[Employee] as a, [dbo].[Class] as b where (a.Emp_no <> b.Emp_no or a.Org_Code <> b.Org_Code or a.Org_Num <> b.Org_Num)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

There is no join criteria there

you need 3 queries as I mentioned

you need to handle nulls if either field you are comparing is nullable

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

rk1980factor SSCommitted Points: 1681 More actions · Answer 4

Best I can come up with is

Select A.Emp_no from employee A

left outer join Class B on (B.Emp_no<> A.Emp_no or b.[Org_Code]<>a.[Org_Code] or b.[Org_Num]<>a.[Org_Num])

where B.Emp_no IS not NULL

but it is still giving me wrong results, wish someone can explain what i am doing it wrong....this is my first experience with join or nested query

rk1980factor SSCommitted Points: 1681 More actions · Answer 5

any help will be great...i really need to resolve this task...at earliest

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

-- get rows which are unique to Employee:

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]

EXCEPT

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]

-- get rows which are unique to Class:

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]

EXCEPT

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]

Edit: Important point about EXCEPT - "When comparing column values for determining DISTINCT rows, two NULL values are considered equal."

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rk1980factor SSCommitted Points: 1681 More actions · Answer 7

this query is incorrect, looks like you mixed up org_code with city

also beside that it gives me emp_no : 43232 in the result which is incorrect since combination of that emp_no with org_code and org_num are exactly same in class table so that emp_no should not be displayed in the result.

rk1980factor SSCommitted Points: 1681 More actions · Answer 8

rk1980factor

SSCommitted

Points: 1681

May 12, 2016 at 8:04 am

#1878673

any help will be great??

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

rk1980factor (5/12/2016)
this query is incorrect, looks like you mixed up org_code with city
also beside that it gives me emp_no : 43232 in the result which is incorrect since combination of that emp_no with org_code and org_num are exactly same in class table so that emp_no should not be displayed in the result.

a) Which query are you referring to?

b) Show where the query references city

c) look at the rows for emp_no : 43232 from each table - ALL of them.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

rk1980factor (5/12/2016)
any help will be great??

I think what ChrisM posted should help you.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

rk1980factor SSCommitted Points: 1681 More actions · Answer 11

1) I was referring to following query which you suggested to resolve the original problem at the top in the first post, this query does not solve the problem

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]

EXCEPT

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]

-- get rows which are unique to Class:

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]

EXCEPT

SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]

2) Also not sure how but when i run your second query it shows "city" in the "Org_Code" column

3) If you see my word document or original post, it ask you to check just 3 columns not all columns if those 3 columns from both tables are exactly same then i do not want to see that employee no. I only want to see the employee numbers from both tables where either each of those 3 columns does not have exact match with same 3 columns from other table or if any of the employee number is missing in any of the table (for e.g there is emp number in table employee but not in table class or emp no in class but not in employee)

please let me know if i need to explain more

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 12

my suggestion is that you read this article please

https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

and then re post with some sample set up scripts....will save any confusion for you and us.

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

rk1980factor (5/12/2016)
1) I was referring to following query which you suggested to resolve the original problem at the top in the first post, this query does not solve the problem
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]
EXCEPT
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]
-- get rows which are unique to Class:
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Class]
EXCEPT
SELECT Emp_no, Org_Code, Org_Num FROM [dbo].[Employee]
2) Also not sure how but when i run your second query it shows "city" in the "Org_Code" column
3) If you see my word document or original post, it ask you to check just 3 columns not all columns if those 3 columns from both tables are exactly same then i do not want to see that employee no. I only want to see the employee numbers from both tables where either each of those 3 columns does not have exact match with same 3 columns from other table or if any of the employee number is missing in any of the table (for e.g there is emp number in table employee but not in table class or emp no in class but not in employee)
please let me know if i need to explain more

Using your own sample data:

DROP TABLE #Employee

CREATE TABLE #Employee ([Emp_no.] INT, [Class_no#] INT, [Org_Code] INT, [Org_Num] INT)

INSERT INTO #Employee VALUES

(48391,44,82038,4323),

(43232,44,33234,5234),

(34565,44,25434,5443),

(53454,0,2353,64343),

(54233,0,8765,52938)

DROP TABLE #Class

CREATE TABLE #Class ([Emp_no.] INT, [Class_no#] INT, [School$] VARCHAR(8), [School#] VARCHAR(4), [Area] INT, [City] VARCHAR(10), [Org_Code] INT, [Org_Num] INT, [State] CHAR(2))

INSERT INTO #Class VALUES

(48391, 44,'$4500', 'A24', 404, 'Liehs', 82038,4423,'GA'),

(43232, 44,'$2000', 'K83', 643, 'Kiore', 33234,5234,'PA'),

(23433, 44,'$3500', 'L53', 201, 'Crosie', 25434,5443,'NY'),

(53454, 0,'$5000', 'S25', 924, 'Byline', 2353,64343,'CA'),

(23454, 0,'$8000', 'I53', 507, 'Train', 8765,52938,'FL'),

(54233, 65,'$2000', 'K83', 643, 'Kiore', 5634,52938,'PA')

-- get rows which are unique to Employee:

SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee

EXCEPT

SELECT [Emp_no.], Org_Code, Org_Num FROM #Class

-- get rows which are unique to Class:

SELECT [Emp_no.], Org_Code, Org_Num FROM #Class

EXCEPT

SELECT [Emp_no.], Org_Code, Org_Num FROM #Employee

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rk1980factor SSCommitted Points: 1681 More actions · Answer 14

Chris, Looks like you solution might work. Only point i forgot to mention was that both the views(tables) are in different database