How to perform Query |A| - Qury |B|

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 2005 Forums

Transact-SQL (2005)

How to perform Query |A| - Qury |B|

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-08 : 05:18:53

Below is Mine First Function...
---------------------------------------------------------------------
SELECT * FROM
Fn_Get_Trade_Broker_Seller_Current_Month(1201, '08/01/2006', '08/30/2008' )

OutPut......
Deal Bro1 BROAMT1 Bro2 BROAMT2 Bro3 BROAMT3 Bro4 BROAMT4
1201 2106 625 2140 625 2123 625 2108 625

Below is Mine Second Function...
---------------------------------------------------------------------

SELECT * FROM
Fn_Get_Trade_Broker_Seller_Prev_Month(1201, '08/01/2006', '08/30/2008' )

OutPut...
Deal Bro1 BROAMT1 Bro2 BROAMT2 Bro3 BROAMT3 Bro4 BROAMT4
1201 2103 625 2111 625 2123 625 2108 625

*********************************************************************

Now i want to perform (Function First - Function Second) with the following output...

Deal Bro1 BROAMT1 Bro2 BROAMT2 Bro3 BROAMT3 Bro4 BROAMT4
Bro5 BROAMT5 Bro6 BROAMT6
1201 2106 625 2140 625 2123 625 2108 625
2103 625 2111 625

IF needed then i can send query inside Function A & inside Function B.

Please, help me....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 05:24:41

[code]SELECT *
FROM
Fn_Get_Trade_Broker_Seller_Current_Month(1201, '08/01/2006', '08/30/2008' ) f1
inner join
Fn_Get_Trade_Broker_Seller_Prev_Month(1201, '08/01/2006', '08/30/2008' ) f2
on f1.Deal = f2.Deal[/code]

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

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-08 : 05:48:24

Thanks khtan.

But all i need is

Output = Fun A MINUS(-) Fun B..

It means...
If A = {2106, 2140, 2123, 2128} And
B = {2103, 2111, 2123, 2108}

Then A - B = {2106, 2140, 2123, 2128, 2103, 2111}

-- Regards
Prashant Hirani

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 05:53:44

quote:
Originally posted by hirani_prashant

Thanks khtan.

But all i need is

Output = Fun A MINUS(-) Fun B..

It means...
If A = {2106, 2140, 2123, 2128} And
B = {2103, 2111, 2123, 2108}

Then A - B = {2106, 2140, 2123, 2128, 2103, 2111}

-- Regards
Prashant Hirani

Don't quite understand. What do you mean by minus ?
You can just specify the column you required from each of the function in the select statement

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 06:45:21

Is it this what you want?

SELECT * 
FROM
    Fn_Get_Trade_Broker_Seller_Current_Month(1201, '08/01/2006', '08/30/2008' ) f1
union 
 SELECT * 
FROM
   Fn_Get_Trade_Broker_Seller_Prev_Month(1201, '08/01/2006', '08/30/2008' ) f2
on  f1.Deal = f2.Deal

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-08 : 07:51:30

quote:

SELECT *
FROM
Fn_Get_Trade_Broker_Seller_Current_Month(1201, '08/01/2006', '08/30/2008' ) f1
union
SELECT *
FROM
Fn_Get_Trade_Broker_Seller_Prev_Month(1201, '08/01/2006', '08/30/2008' ) f2
on f1.Deal = f2.Deal

Is that the same as


SELECT DISTINCT *
FROM
    Fn_Get_Trade_Broker_Seller_Current_Month(1201, '08/01/2006', '08/30/2008' ) f1
inner join
    Fn_Get_Trade_Broker_Seller_Prev_Month(1201, '08/01/2006', '08/30/2008' ) f2
on  f1.Deal = f2.Deal

Regardless....

I don't understand what you are wanting exactly..

you said...

quote:

Output = Fun A MINUS(-) Fun B..

It means...
If A = {2106, 2140, 2123, 2128} And
B = {2103, 2111, 2123, 2108}

Then A - B = {2106, 2140, 2123, 2128, 2103, 2111}

-- Regards
Prashant Hirani

It looks (at first glance) like your A-B contains only the distinct entries (1 copy of each if they are in either) but then the value 2108 should be in the list and it isn't. Is this a typo?

Yours Confused.

-------------
Charlie

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-08 : 08:27:23

quote:
Fun A MINUS(-) Fun B

One way to write that in SQL is simply:

select a.value
from a
left outer join b on a.value=b.value
where b.value is null

Put a DISTINCT after the SELECT if you want only distinct values from A to be returned.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources