CODEWITHSUNDEEP
sqlsql-server
intrigued_66
intrigued_66

Reputation: 17240

Subtracting two SELECT statements to produce a single result?

I did find this post: how do I subtract values from two select statements

but I cannot get it to work on SQL-Server 2008.

I have two basic SELECT statements. No GROUP BYs or anything, just a SELECT, FROM and WHERE.

I would like to subtract one of these SELECTs from the other. Both return one row and column.

Could somebody please help?

SELECT 
    ((SELECT Field1 AS BaseBase FROM Table1
        WHERE Field2 = 'something' 
        AND Field3 = 
           (SELECT Field4 FROM SEL_Function('something','something')) as tab1)
      -     
    ((SELECT Field5 FROM SEL_Function('something','something') as tab2)

Upvotes: 1

Views: 9715

Answers (2)

JStead
JStead

Reputation: 1730

Alright I don't think the cross apply is necessary fiddle won't allow functions but check out this fiddle. You will want to swap out the table of the same name with the function. I think you were getting bit by a combination of alias and paren mismatches.

http://sqlfiddle.com/#!3/dc72f/13

Upvotes: 3

Betaminos
Betaminos

Reputation: 482

In the provided link there is

select v1.Value1 - v2.Value2 from ((bla) AS v1 CROSS JOIN (blubb) AS v2

which I would translate to this (in your case)

SELECT tab1.BaseBase - tab2.Minus FROM 
((SELECT Field1 AS BaseBase FROM Table1
    WHERE Field2 = 'something' 
    AND Field3 = 
       (SELECT Field4 FROM SEL_Function('something','something')) as tab1
CROSS JOIN 
(SELECT Field5 AS Minus FROM SEL_Function('something','something')) as tab2)

Like stated in the comments, it would be advisable to specify the affected table. However, I think that it might work (I have no testing system at hand :( ) without those explicit definitions as well, because the names are unique.

Upvotes: 0

Related Questions