Apply Join on a table with no unique index

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

General SQL Server Forums

New to SQL Server Programming

Apply Join on a table with no unique index

Author

Topic

cheesecake
Starting Member

11 Posts

Posted - 2010-08-12 : 12:56:40

Hi,
I have a table "Test" with 2 columns VehicleId and StandardDeviation and the data looks like

VehicleId Std.Dev.
500 2
500 2
600 3
600 3

I have a copy of this table which has fewer rows but similar data i.e. duplicate values. I am wondering how can I find the difference between these two tables. I want to publish the difference between these two in a diff table.

Thanks,
CZCaKe

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 13:08:12

[code]
SELECT t1.*
FROM Test t1
LEFT JOIN Test_Copy tc
ON t1.VehicleId = tc.VehicleId
WHERE tc.VehicleId IS NULL
[/code]
will give records in main table which are not in copy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-12 : 14:28:47

quote:

I want to publish the difference between these two in a diff table.

Difference as in

1). Rows in Table 1, Not in Table 2
2). Rows in Table 2, Not in Table 1
3). Rows in Table 1 that are the same, but data is different

Is that what you want?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cheesecake
Starting Member

11 Posts

Posted - 2010-08-12 : 16:52:35

Brett, That's correct

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-12 : 17:39:02

quote:
. I am wondering how can I find the difference between these two tables

If you use SQL Server 2005/2008 you can use EXCEPT set operator for achieving this.
Example:

SELECT * FROM table_1
EXCEPT
SELECT * FROM table_2

______________________

cheesecake
Starting Member

11 Posts

Posted - 2010-08-12 : 18:57:39

Thanks for the suggestion but I want to know how can I determine the difference using JOINS

Difference as in

1). Rows in Table 1, Not in Table 2
2). Rows in Table 2, Not in Table 1
3). Rows in Table 1 that are the same, but data is different

Thx,
CZCake

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources