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
 NOT EXISTS Two Tables

Author  Topic 

Stark
Starting Member

7 Posts

Posted - 2010-10-09 : 16:27:04
Hi , have a good day ,

I have two Tables Persons1 and Persons2 which has the same structure
I need to select the the values from Persons1 Which is not exsits in Persons2!


In other Word :
Show me the result where ( Persons1.ID <> Persons2.ID AND Persons1.Name <> Persons2.Name AND Persons1.Age <> Persons2.Age )


Here is my tables :

Persons1
SELECT ID , NAME , AGE FROM Persons1
---------------------
ID | NAME | AGE
---------------------
1 |Stark | 26
1 |Stark | 25
2 | Sonya | 27
3 | Kabal | 22
4 | Jade | 25

Persons2
SELECT ID , NAME , AGE FROM Persons2
---------------------
ID | NAME | AGE
---------------------
1 |Stark | 26
2 | Sonya | 27


Final Result Should be like this !
---------------------
ID | NAME | AGE
---------------------
1 | Stark | 25
3 | Kabal | 22
4 | Jade | 25


P.S :
I have tried this :
SELECT * FROM persons1 WHERE NOT EXISTS (SELECT * FROM persons2) -- But it didn't show any results :(


SELECT * FROM Persons1 JOIN persons2
ON persons1.ID <> Persons2.ID AND persons1.Name <> Persons2.NAme AND persons1.Age <> Persons2.Age -- Also it shows all the result :(


I am using SQL 2000

I know nothing , I know nothing ....

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-09 : 16:49:13
Try this:

SELECT ID, Name, Age
FROM persons1 AS P1
WHERE NOT EXISTS(SELECT *
FROM persons2 AS P2
WHERE P2.ID = P1.ID
AND P2.Name = P1.NAme
AND P2.Age = P1.Age)
Go to Top of Page

Stark
Starting Member

7 Posts

Posted - 2010-10-09 : 16:59:08
quote:
Originally posted by malpashaa

Try this:

SELECT ID, Name, Age
FROM persons1 AS P1
WHERE NOT EXISTS(SELECT *
FROM persons2 AS P2
WHERE P2.ID = P1.ID
AND P2.Name = P1.NAme
AND P2.Age = P1.Age)




Dear Sir,
Thank you so much , it's work like charm !

Allah Ya3tek el3afeh


I know nothing , I know nothing ....
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-09 : 18:22:01
Ahlan wa sahlan
Go to Top of Page
   

- Advertisement -