put two SP results together

[Jamesy]

Hi ,
I have two stored procedures in my MSSQL database.

each looks at the individual operators (loginID) for an admin system, then
checks that ID against the orders in the db.. it then creates an result set
which gives me the loginId, the number of orders that login has placed and
also the total income generated.

The reason there are two is that there are two orders table - a trade one
and a retail one.

I would very much like to be able to have one SP create all results for me
so:

LoginID | RetailNoOrders |RetailIncome |TradeNoOrders | TradeIncome


Any ideas?

James

[Lionstone]

Use a left join.
Loosely -
SELECT L.LoginID, COUNT(R.OrderNo), SUM(R.Income), COUNT(T.OrderNo),
SUM(T.Income)
FROM dbo.UserLogins L
LEFT OUTER JOIN RetailOrders R ON L.LoginID=R.LoginID
LEFT OUTER JOIN TradeOrders T ON L.LoginID=T.LoginID
ORDER BY L.LoginID

Notice I used a specific field in the counts, not *. That's very important
to get the right order counts.


"Jamesy" <NOSPAM@patchworkmedia.co.uk> wrote in message
news:d4t7on$3tu$1@forums.macromedia.com...

> Hi ,
> I have two stored procedures in my MSSQL database.
>
> each looks at the individual operators (loginID) for an admin system, then
> checks that ID against the orders in the db.. it then creates an result
> set which gives me the loginId, the number of orders that login has placed
> and also the total income generated.
>
> The reason there are two is that there are two orders table - a trade one
> and a retail one.
>
> I would very much like to be able to have one SP create all results for me
> so:
>
> LoginID | RetailNoOrders |RetailIncome |TradeNoOrders | TradeIncome
>
>
> Any ideas?
>
> James
>
>

[Jamesy]

Cheers - thanks very much

James


"Lionstone" <HIDElionstoneHIDE@HIDEhush.com> wrote in message
news:d4tc93$apr$1@forums.macromedia.com...

> Use a left join.
> Loosely -
> SELECT L.LoginID, COUNT(R.OrderNo), SUM(R.Income), COUNT(T.OrderNo),
> SUM(T.Income)
> FROM dbo.UserLogins L
> LEFT OUTER JOIN RetailOrders R ON L.LoginID=R.LoginID
> LEFT OUTER JOIN TradeOrders T ON L.LoginID=T.LoginID
> ORDER BY L.LoginID
>
> Notice I used a specific field in the counts, not *. That's very
> important to get the right order counts.
>
>
> "Jamesy" <NOSPAM@patchworkmedia.co.uk> wrote in message
> news:d4t7on$3tu$1@forums.macromedia.com...

>> Hi ,
>> I have two stored procedures in my MSSQL database.
>>
>> each looks at the individual operators (loginID) for an admin system,
>> then checks that ID against the orders in the db.. it then creates an
>> result set which gives me the loginId, the number of orders that login
>> has placed and also the total income generated.
>>
>> The reason there are two is that there are two orders table - a trade one
>> and a retail one.
>>
>> I would very much like to be able to have one SP create all results for
>> me so:
>>
>> LoginID | RetailNoOrders |RetailIncome |TradeNoOrders | TradeIncome
>>
>>
>> Any ideas?
>>
>> James
>>
>>

>
>