using minus function - 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

SQL Server 2000 Forums

SQL Server Development (2000)

using minus function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-01 : 09:21:13

shivaram writes "they are two tables
table_one
number value
1 a
12 b
1 a
2 c
2 c

table_two
number value
14 xyz
1 zxc
2 abc

The question is
when table one minu from table one
i.e
select number from table_one
minus
select number from table_two
the result should be
1
2
12

what is the eqivalent of minus in sqlserver"

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-04-01 : 09:46:16

select number
from table_one
Where number not in (Select Distinct number from table_two)

Jeremy

Jay99

468 Posts

Posted - 2002-04-01 : 09:48:42

quote:

select number
from table_one
Where number not in (Select Distinct number from table_two)

That would only product 12 right? not 1 and 2.

I don't understand what the original poster wants. Could you post some context? I think I may remember a MINUS statement from my Oracle days, but I don't remember how it worked . .

Jay
<O>

izaltsman
A custom title

1139 Posts

Posted - 2002-04-01 : 10:38:53

Yup... MINUS in Oracle would do the trick... But since SQL Server doesn't have the MINUS function, Guru's Guide to Transact-SQL offers a workaround (yes, I'm out to steal Rob's thunder!

)


select x.number
from (select 	t1.number
		, count(*) as num1
		, (select COUNT(*) FROM tbl2 t2 WHERE t2.number = t1.number) as num2
	from
		tbl1 t1 
       group by t1.number) as x
group by x.number
having (abs(sum(num1)-sum(num2))>0)

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Edited by - izaltsman on 04/01/2002 10:39:35

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources