Table Compare Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table Compare Help

Author

Topic

fubuki99
Starting Member

10 Posts

Posted - 2009-07-09 : 19:05:23

Greetings,

I have two tables with similar data that I need to compare the values and display the result of the fallouts. The tables are as follows:

customer table:

phonenumber cu_esn cu_company

2813286236 110 VERIZ
2813284182 110 VERIZ
2813282219 110 VERIZ
2813283466 110 CMCST
2813282775 1113 VERIZ
2813287128 110 CMCST

PhoneNumber char(10) Unchecked
cu_esn char(5) Unchecked
cu_company char(5) Unchecked

Table RSWB:

Phonenumber ESN cu_company
2813286236 110 VERIZ
2813284182 110 VERIZ
2813282219 110 VERIZ
2813283466 110 VERIZ
2813282775 110 VERIZ
2813287128 110 VERIZ

PhoneNumber nvarchar(13) Checked
ESN nvarchar(3) Checked
cu_company nvarchar(6) Checked
Unchecked

I need the fallout of any results if any of the matching phone numbers in the the two tables have different ESN or cu_company values. Thanks!

singularity
Posting Yak Master

153 Posts

Posted - 2009-07-09 : 19:39:10

select phonenumber
from
(select phonenumber, cu_esn, cu_company
from customer table
union all
select phonenumber, cu_esn, cu_company
from RSWB) a
group by phonenumber
having count(distinct cu_esn) > 1 or count(distinct cu_company) > 1

fubuki99
Starting Member

10 Posts

Posted - 2009-07-10 : 00:52:59

I'm getting the below error on your statement:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'table'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

Basically, the result should return the following values to me

2813283466 110 CMCST
2813282775 1113 VERIZ

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-10 : 01:29:49

[code]
select c.* from customer c
left join rswb r on r.esn = c.cu_esn and r.cu_company = c.cu_company
where r.esn is null

select * from customer c where not exists (select * from rswb where esn = c.cu_esn and cu_company = c.cu_company)
[/code]

singularity
Posting Yak Master

153 Posts

Posted - 2009-07-10 : 09:29:19

quote:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'table'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

You have to replace "customer table" in my code with the actual name of your table.

fubuki99
Starting Member

10 Posts

Posted - 2009-07-10 : 20:10:05

correct, I did and the query ran but it returned all of the rows in the customer table even the ones that don't match the RSWB table. Below is my join statement that only return the rows that match the RSWB table and I'm trying to figure out how to return the result sets only for values that are not matching for each column.

SELECT Customer.PhoneNumber as Cu_PhoneNumber, RSWB.PhoneNumber AS RSWB_PHoneNumber, Customer.cu_esn as Cu_ESN, RSWB.ESN
as RSWB_ESN, Customer.cu_company AS Cust_Company, RSWB.cu_company as RSWB_Company

FROM Customer right outer JOIN
RSWB ON Customer.PhoneNumber = RSWB.PhoneNumber

Output:

Cu_PhoneNumber RSWB_PHoneNumber Cu_ESN RSWB_ESN Cust_Company RSWB_Company
2813283743 2813283743 110 110 VERIZ VERIZ
2813281828 2813281828 110 110 VERIZ VERIZ
2813286059 2813286059 110 110 VERIZ VERIZ
2813286490 2813286490 110 110 VERIZ VERIZ
2813287132 2813287132 110 110 CMCST CMCST
2813288843 2813288843 110 110 VERIZ VERIZ
2813284204 2813284204 110 110 CMCST VERIZ
2813282820 2813282820 110 110 VERIZ VERIZ
2813285841 2813285841 110 110 VERIZ VERIZ
NULL 2813289135 NULL 110 NULL VERIZ
NULL 2813283081 NULL 110 NULL VERIZ
NULL 2813288373 NULL 109 NULL VERIZ

I need the output to display only the results when columns that don't match each other in the table e.g.

output:

Cu_PhoneNumber RSWB_PHoneNumber Cu_ESN RSWB_ESN Cust_Company RSWB_Company
2813284204 2813284204 110 110 CMCST VERIZ
NULL 2813289135 NULL 110 NULL VERIZ
NULL 2813283081 NULL 110 NULL VERIZ
NULL 2813288373 NULL 109 NULL VERIZ

Thanks for the help!

singularity
Posting Yak Master

153 Posts

Posted - 2009-07-10 : 20:29:55

SELECT Customer.PhoneNumber as Cu_PhoneNumber, RSWB.PhoneNumber AS RSWB_PHoneNumber, Customer.cu_esn as Cu_ESN, RSWB.ESN
as RSWB_ESN, Customer.cu_company AS Cust_Company, RSWB.cu_company as RSWB_Company

FROM Customer right outer JOIN
RSWB ON Customer.PhoneNumber = RSWB.PhoneNumber

WHERE Customer.PhoneNumber is null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 20:30:07

[code]
SELECT Customer.PhoneNumber as Cu_PhoneNumber, RSWB.PhoneNumber AS RSWB_PHoneNumber, Customer.cu_esn as Cu_ESN, RSWB.ESN
as RSWB_ESN, Customer.cu_company AS Cust_Company, RSWB.cu_company as RSWB_Company

FROM Customer right outer JOIN
RSWB ON Customer.PhoneNumber = RSWB.PhoneNumber

WHERE Customer.PhoneNumber IS NULL

[/code]

KH
[spoiler]Time is always against us[/spoiler]

fubuki99
Starting Member

10 Posts

Posted - 2009-07-11 : 23:29:12

We're almost there, but the statements suggested last and previous only returns the null values. Let me clarify my desired result. Basically, I need all the phonenumber column in RSWB compared against the customer table phonenumber column. My join statement does this and it returns 5,539 rows. However, I only want to view the rows that are not an exact match against the RSWB table and output that result e.g. if the phonenumber,cu_ESN,cu_company are different or has null value from the customer table then output the results.

As referenced from the below table:

Cu_PhoneNumber RSWB_PHoneNumber Cu_ESN RSWB_ESN Cust_Company RSWB_Company
2813283743 2813283743 110 110 VERIZ VERIZ
2813281828 2813281828 110 110 VERIZ VERIZ
2813286059 2813286059 110 110 VERIZ VERIZ
2813286490 2813286490 110 110 VERIZ VERIZ
2813287132 2813287132 110 110 CMCST CMCST
2813288843 2813288843 110 110 VERIZ VERIZ
2813284204 2813284204 110 110 CMCST VERIZ
2813282820 2813282820 110 110 VERIZ VERIZ
2813285841 2813285841 110 110 VERIZ VERIZ
NULL 2813289135 NULL 110 NULL VERIZ
NULL 2813283081 NULL 110 NULL VERIZ
NULL 2813288373 NULL 109 NULL VERIZ

the following output and subsequent match exact on every column from the RSWB table against the customer table:

Cu_PhoneNumber RSWB_PHoneNumber Cu_ESN RSWB_ESN Cust_Company RSWB_Company
2813283743 2813283743 110 110 VERIZ VERIZ

I don't want that output result:

I want the following :

Cu_PhoneNumber RSWB_PHoneNumber Cu_ESN RSWB_ESN Cust_Company RSWB_Company
2813284204 2813284204 110 110 CMCST VERIZ
NULL 2813289135 NULL 110 NULL VERIZ
NULL 2813283081 NULL 110 NULL VERIZ
NULL 2813288373 NULL 109 NULL VERIZ

These outputs tell me that the customer table has either null values matching the phonenumber, or cu_esn, or cust_company are different than what the RSWB table has values for. I hope I didn't confuse the matter even more but again I appreciate the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 04:03:33

[code]
SELECT Customer.PhoneNumber as Cu_PhoneNumber, RSWB.PhoneNumber AS RSWB_PHoneNumber, Customer.cu_esn as Cu_ESN, RSWB.ESN
as RSWB_ESN, Customer.cu_company AS Cust_Company, RSWB.cu_company as RSWB_Company

FROM RSWB
left outer JOIN Customer
ON Customer.PhoneNumber = RSWB.PhoneNumber
WHERE COALESCE(Cu_ESN,'Unknown')<>COALESCE(RSWB_ESN,'Unknown')
OR COALESCE(Cust_Company,'Unknown')<>COALESCE(RSWB_Company,'Unknown')
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources