Subtract Two Tables - 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

General SQL Server Forums

New to SQL Server Programming

Subtract Two Tables

Author

Topic

Stark
Starting Member

7 Posts

Posted - 2010-11-01 : 18:50:33

Hi , have a good day , ( I am using Sql 2000 )

And I have Two Tables


Table1
----------------------
ID       |   Name   
----------------------
1        |  Stark
3        |  Kabal
2        |  Sonya 
4        |  Rain

-----------------------------------


Table2
----------------------
ID       |   Name   
----------------------
1        |  Stark
2        |  Sonya
1        |  Stark

I want to Show the result as :


-------------------
ID       |  Name 
-------------------
1        | Stark        
3        | Kabal
4        | Rain

As far as I get :


SELECT Table1.ID , Table1.Name  FROM table1
LEFT JOIN table2
ON table1.ID = table2.ID 
AND table1.Name = Table2.Name 
WHERE table2.ID IS NULL

But it's only shows


-------------------
ID       |  Name 
-------------------
3        | Kabal
4        | Rain

P.S :
Every Row at Table1 , Remove Only One Row from Table2 ,
And the un-removed from Table1 goes to the result !

Kind regards , Any help will appreciated so much

I know nothing , I know nothing ....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-01 : 20:20:32

quote:
Every Row at Table1 , Remove Only One Row from Table2 ,
And the un-removed from Table1 goes to the result !

based on your requirement above, can you explain how would "Stark" appear in the result ?

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

Stark
Starting Member

7 Posts

Posted - 2010-11-02 : 03:21:18

quote:
Originally posted by khtan

quote:
Every Row at Table1 , Remove Only One Row from Table2 ,
And the un-removed from Table1 goes to the result !

based on your requirement above, can you explain how would "Stark" appear in the result ?

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

Hi , thank you for replay

"Stark" appear in the result , because we have two rows in table1 Called "Stark" and one row in Table2 Called "Stark" , thus ,
Only one row from table1 And table2 Will be removed , and the remaining Row "Stark" at Table1 will go to the result

it's like
IF (Table1.Row Exist at Table2)
{
// remove table1.row , remove only one row from table2
}
else
{
// send table1.Row to result
}

Hope this could explain more

I know nothing , I know nothing ....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-02 : 22:08:24

Will this do ?

declare @Table1 table
(
	ID	int,
	Name	varchar(5)
)

declare @Table2 table
(
	ID	int,
	Name	varchar(5)
)

insert into @Table1
select	1, 'Stark'	union all
select	3, 'Kabal'	union all
select	2, 'Sonya'	union all
select	4, 'Rain'

insert into @Table2
select	1, 'Stark'	union all
select	2, 'Sonya'	union all
select	1, 'Stark'

select	t1.*
from	@Table1 t1
	left join
	(
		select	ID, Name, cnt = count(*)
		from	@Table2
		group by ID, Name
	) t2	on	t1.ID	= t2.ID
		and	t1.Name	= t2.Name
where	t2.Name	is null
or	t2.cnt	> 1

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-11-03 : 03:36:55

quote:

Originally posted by khtan

Will this do ?

declare @Table1 table
(
	ID	int,
	Name	varchar(5)
)

declare @Table2 table
(
	ID	int,
	Name	varchar(5)
)

insert into @Table1
select	1, 'Stark'	union all
select	3, 'Kabal'	union all
select	2, 'Sonya'	union all
select	4, 'Rain'

insert into @Table2
select	1, 'Stark'	union all
select	2, 'Sonya'	union all
select	1, 'Stark'

select	t1.*
from	@Table1 t1
	left join
	(
		select	ID, Name, cnt = count(*)
		from	@Table2
		group by ID, Name
	) t2	on	t1.ID	= t2.ID
		and	t1.Name	= t2.Name
where	t2.Name	is null
or	t2.cnt	> 1

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

If same row will repeat again (more than two times) in second table.
1, 'Stark'

above query will not include all the remaining rows.
it will consider only one row.

If the requirement is like this that all the remaining rows should be include in result set
can go for -


declare @Table1 table
(
	ID	int,
	Name	varchar(5)
)

declare @Table2 table
(
	ID	int,
	Name	varchar(5)
)

insert into @Table1
select	1, 'Stark'	union all
select	3, 'Kabal'	union all
select	2, 'Sonya'	union all
select	4, 'Rain'

insert into @Table2
select	1, 'Stark'	union all
select	2, 'Sonya'	union all
select	1, 'Stark'  union all
select	1, 'Stark'

select	t1.*
from	@Table1 t1
	left join
	(
		select	ID, Name, ROW_NUMBER() OVER(PARTITION BY ID, NAME ORDER BY ID ) Seq
		from	@Table2
	) t2	on	t1.ID	= t2.ID
		and	t1.Name	= t2.Name
where	t2.ID is null OR t2.Seq > 1

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources