comparing values within database

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

comparing values within database

Author

Topic

albaker
Starting Member

7 Posts

Posted - 2007-09-10 : 07:41:03

Hi,

There are two tables in my Database, tb1 and tb2 which both have the same attribute ID. I would like to ensure that there is nothing in ID in tb1 which is not listed in ID in tb2, can anyone help?

Thanks for any info.

Albert.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 07:44:44

SELECT t1.*
FROM Table1
LEFT JOIN Table2 ON Table2.ID = Table1.ID
WHERE Table2.ID IS NULL

E 12°55'05.25"
N 56°04'39.16"

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-09-10 : 07:44:56

Use foreign key constraint.

Necessity is the mother of all inventions!

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-09-10 : 07:46:34

DELETE FROM tb1
WHERE ID NOT IN (SELECT ID FROM tb2)

Necessity is the mother of all inventions!

albaker
Starting Member

7 Posts

Posted - 2007-09-10 : 09:24:02

Hi, sorry, am having trouble understanding SELECT t1.*
FROM Table1, is this suppossed to be ID instead of t1 i.e., column name?

Thanks for your advice on this

quote:
Originally posted by Peso

SELECT t1.*
FROM Table1
LEFT JOIN Table2 ON Table2.ID = Table1.ID
WHERE Table2.ID IS NULL

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-10 : 11:53:27

[quote]Originally posted by albaker

Hi, sorry, am having trouble understanding SELECT t1.*
FROM Table1, is this suppossed to be ID instead of t1 i.e., column name?

Thanks for your advice on this

I think he was using t1 as an alias, but maybe forgot to create the alias?

SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.ID = t1.ID
WHERE t2.ID IS NULL

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 12:27:09

"Hi, sorry, am having trouble understanding SELECT t1.*
FROM Table1, is this suppossed to be ID instead of t1 i.e., column name?"

As Zoroaster said, or if you find it easier to understand:

SELECT Table1.*
FROM Table1
LEFT JOIN Table2 ON Table2.ID = Table1.ID
WHERE Table2.ID IS NULL

Kristen

albaker
Starting Member

7 Posts

Posted - 2007-09-10 : 12:48:22

Thanks,

this returns a list of what is different I am presuming, is it possible to return a true or false and how is this done?

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 13:24:37

SELECT Table1.*,
CASE WHEN Table2.ID IS NULL THEN 'Missing' ELSE 'Present' END AS MatchingResult
FROM Table1
LEFT JOIN Table2 ON Table2.ID = Table1.ID

Maybe if you posted your real problem, we might be able to help you better.

E 12°55'05.25"
N 56°04'39.16"

subrata4allfriends
Starting Member

24 Posts

Posted - 2007-09-10 : 14:39:53

Use the following query .....hope it will resolve the same.
The query will return those ID which are not present into another table.

SELECT ID FROM TB1
WHERE ID NOT IN (SELECT ID FROM TB2)

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-10 : 20:33:35

quote:
Originally posted by albaker

Thanks,

this returns a list of what is different I am presuming, is it possible to return a true or false and how is this done?

Cheers

Based on your first post it sounds like you are maybe looking for a way to remove the extra ID's? I don't quite understand what you mean by "a true or false", do you mean if there are rows that should not be there to return true, else return false without any further information?

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources