minus two queries - 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

Transact-SQL (2000)

minus two queries

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2005-04-07 : 08:17:20

hi,

i have to queries and want to minus nit how i do it just like oracle minus works.

select code,rno from table1
minus
select code,rno from table2

how i can do this.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-07 : 09:04:10

select code,rno from table1
where code not in (select code from table2)

andy8979
Starting Member

36 Posts

Posted - 2005-04-07 : 09:32:53

Hi,
There is no equivalent to the minus operator of oracle in sql server as per my knowledge,

Instead you could use the Not exists claues.

select code,rno from table1 As t1
Where Not exists(select code,rno from table2 AS t2 Where t1.rno = t2.rno)

Or try using the outer joins with appropriate where clause

Anuj

andy8979
Starting Member

36 Posts

Posted - 2005-04-07 : 09:48:07

I would suggest you to prefer the outer joins as they would be faster then the not in or the exists clauses

Anuj

imughal
Posting Yak Master

192 Posts

Posted - 2005-04-08 : 00:32:46

hi i m using following query but getting error on where clause. in period and section. tell me how to concatnate string with string.

i have to prepare query and then execute.

Declare @buildStmt1 varchar(2000)
Declare @buildStmt2 varchar(2000)
Declare @peroid varchar(50)
Declare @section varchar(10)

set @peroid='s-2005'
set @section='A'

Set @buildStmt1 = 'SELECT DISTINCT adm_master.CODE, adm_master.RNO FROM adm_master INNER JOIN' +
' adm_detail ON adm_master.CCODE = adm_detail.CCODE AND adm_master.PERIOD = adm_detail.PERIOD' +
' WHERE (adm_master.P_CODE = 1) AND (adm_master.SHIFT = 1) AND (adm_master.CCODE = 1) AND ' +
' (adm_detail.S_CODE = 1) and adm_master.PERIOD='+ @peroid

Set @buildStmt2 = 'SELECT code, rno FROM eval_res_master ' +
' WHERE (p_code = 1) AND (std_section ='+ @section +') AND ' +
' (ccode = 1) AND (period ='+ @peroid + ') AND (s_code = 1)'

EXEC (@buildStmt1)
EXEC (@buildStmt2)

After That i will apply not exist command . how to fix it.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-08 : 00:53:06

@peroid is a varchar

you need to enclose it in quotes. eg

@stmtm = 'adm_master.PERIOD='+ '''' + @peroid + ''''

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources