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 2000 Forums
 Transact-SQL (2000)
 Please Help Me ...

Author  Topic 

skiski
Starting Member

15 Posts
Posted - 2010-05-10 : 02:45:21
i need control two table (each table in (id,number,pay)) number and pay and get result each number and pay not exists in table 1

thanks

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-05-10 : 02:59:57
Number and pay these are the two tables or what???
Not getting you...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

skiski
Starting Member

15 Posts
Posted - 2010-05-10 : 03:20:19
Declare @Table1 table(Id int identity,Number INT,pay INT)
Declare @Table2 table(Id int identity,Number INT,pay INT)

Insert into @Table1
Select 10,1000 union all
Select 20,3000 union all
Select 30,4000 union all
Select 40,5000 union all
Select 50,8000 union all
Select 60,9000

Insert into @Table2
Select 10,1000 union all
Select 30,4000 union all
Select 60,9000 union all
Select 80,6500 union all
Select 200,85 union all
Select 1000,300
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-10 : 03:59:59
What is your expected result?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

skiski
Starting Member

15 Posts
Posted - 2010-05-10 : 04:33:31
Declare @Table1 table(Id int identity,Number INT,pay INT)
Declare @Table2 table(Id int identity,Number INT,pay INT)

Insert into @Table1
Select 10,1000 union all
Select 20,3000 union all
Select 30,4000 union all
Select 40,5000 union all
Select 50,8000 union all
Select 60,9000

Insert into @Table2
Select 10,1000 union all
Select 30,4000 union all
Select 60,9000 union all
Select 80,6500 union all
Select 200,85 union all
Select 1000,300

i need control two table (each table in (id,number,pay)) number and pay and get result each number and pay not exists in table 1

thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-10 : 04:49:40

select t1.* from @table1 as t1 where not exists
(select * from @table2 where t1.id=t2.id and t1.pay=pay)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-05-10 : 05:58:00
quote:
Originally posted by madhivanan


select t1.* from @table1 as t1 where not exists
(select * from @table2 where t1.id=t2.id and t1.pay=pay)

Madhivanan

Failing to plan is Planning to fail


No need of red part

and another way using left join


select t1.* from @table1 as t1 
left join @table2 t2 on t1.id = t2.id and t1.pay = t2.pay
where t2.id is null and t2.pay is null


Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-05-10 : 09:08:07
quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by madhivanan


select t1.* from @table1 as t1 where not exists
(select * from @table2 where t1.id=t2.id and t1.pay=pay)

Madhivanan

Failing to plan is Planning to fail


No need of red part

and another way using left join


select t1.* from @table1 as t1 
left join @table2 t2 on t1.id = t2.id and t1.pay = t2.pay
where t2.id is null and t2.pay is null


Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Yes. It was typing error

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

skiski
Starting Member

15 Posts
Posted - 2011-04-05 : 23:37:13
Thanks
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site