Coalescing data from more than 2 tables

I have a query, below, -Can anyone tell me how I could modify this to
add data from third and fourth tables. Many Thanks
----
select
coalesce (x.NHPART, y.TPROD)
, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
, isnull(y.TQTY,0) as "TQTY (ITH)"
from
(
select
NHPART
, isnull(sum (NCQNTY),0) NCQNTY
from
NCM
WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
='5220'
group by
NHPART
)
x full join
(
select
TPROD
, isnull(sum (TQTY),0) TQTY
from
ITH
WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
group by
TPROD
) y on x.NHPART = y.TPROD
ORDER BY coalesce (x.NHPART, y.TPROD)
----By the time I get to a three way (or worse, four way) full outer join
I find all the equality tests start to get overwhelming - to the point
I have found it hard to be sure my code is written properly.
Eventually I found an alternative way to code them that I, at least,
find much simpler.
I start by writing a query that UNIONs together just the keys - which
is to say the columns used for the IN clause comparisons - from all
the data sources.
select NHPART
from NCM
where NHSET ='INSP'
and NHCDAT between 20060201
and 20060228
and HVNDR >='5220'
UNION
select TPROD
from ITH
WHERE TTDTE between 20060201
and 20060228
and TVEND ='5220'
UNION
select OTHERPROD
from OTHERTBL
WHERE OTHERCOLUMN = 'test value'
Note that UNION performs a DISTINCT, which we need.
Now, with that working, I use that as a derived table, and make it the
root table in a LEFT outer join rather than a FULL outer join:
select K.EitherPart,
coalesce(X.NCQNTY,0) as "NCQNTY (NCM)",
coalesce(Y.TQTY,0) as "TQTY (ITH)",
coalesce(Z.SOMENUMBER) as OtherQty
from (<query from above> ) as K
LEFT OUTER
JOIN (<your first derived table> ) as X
ON K.EitherPart = X.NHPART
LEFT OUTER
JOIN (<your second derived table> ) as Y
ON K.EitherPart = Y.TPROD
LEFT OUTER
JOIN (<yet another derived table> ) as Z
ON K.EitherPart = Z.OTHERPROD
I hope this helps.
Roy Harvey
Beacon Falls, CT
On 7 Mar 2006 03:16:37 -0800, "philipbennett25" <pbennett@.xyratex.com>
wrote:

>I have a query, below, -Can anyone tell me how I could modify this to
>add data from third and fourth tables. Many Thanks
>----
>select
> coalesce (x.NHPART, y.TPROD)
>, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
>, isnull(y.TQTY,0) as "TQTY (ITH)"
>from
>(
>select
> NHPART
>, isnull(sum (NCQNTY),0) NCQNTY
>from
> NCM
>WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
>='5220'
>group by
> NHPART
> )
>x full join
>(
>select
> TPROD
>, isnull(sum (TQTY),0) TQTY
>from
> ITH
>WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
>group by
> TPROD
> ) y on x.NHPART = y.TPROD
>ORDER BY coalesce (x.NHPART, y.TPROD)
>----

Clustering with sql and third party database softwares

I have a clustered enviroment made of 2 win2k advanced servers
clustered in a network. Servers are running sql 2000 that hosts a third
party database X. Now X was working perfectly until these servers were
clustered. Users accessed X via a citrix client. Any idea as to why
accessing this third party database (X) became slower after being
placed in a clustered enviroment? Please feel free to come back with
any solutions.
Thanks
Hi
Did you have the exact same hardware setup before you clustered? A badly
implemented SAN can blow your performance.
Is each instance allocated the same amount of RAM as before clustering?
Run performance monitor and see what type of bottlenecks are shown. You did
not give much information about what is slower so I can't really pin point
the problem.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dafeuwotu@.yahoo.co.uk> wrote in message
news:1121623805.177041.48990@.f14g2000cwb.googlegro ups.com...
>I have a clustered enviroment made of 2 win2k advanced servers
> clustered in a network. Servers are running sql 2000 that hosts a third
> party database X. Now X was working perfectly until these servers were
> clustered. Users accessed X via a citrix client. Any idea as to why
> accessing this third party database (X) became slower after being
> placed in a clustered enviroment? Please feel free to come back with
> any solutions.
> Thanks
>

Clustering of SQL Server 2000 SE

We are looking at clustering of SQL Server 2000 [standard Edition] using third party software, any inputs in this regard would be of great helpPlease post your question to the right forum: SQL Server Disaster Recovery and Availability

clustering - adding a third node

Hi All,
I have a two node cluster, and need to add a third node, can anyone please
send me a link, or steps on howto.
url:http://www.ureader.com/gp/1142-1.aspx
The steps are documented in the BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fe20dca9-a4c1-4d32-813d-42f1782dfdd3.htm
Linchi
"achilles silva" wrote:

> Hi All,
> I have a two node cluster, and need to add a third node, can anyone please
> send me a link, or steps on howto.
> url:http://www.ureader.com/gp/1142-1.aspx
>
|||Thank you, read about that, forgot to mention in detail that I am working on
a windows 2003 sp2 and sql 2000 sp4 still.
url:http://www.ureader.com/msg/11421002.aspx
|||For SQL2000, the steps are documented in its BOL under the heading "How to
add nodes to an existing virtual server (Setup)". See also
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx#ELUAG
Linchi
"achilles silva" wrote:

> Hi All,
> I have a two node cluster, and need to add a third node, can anyone please
> send me a link, or steps on howto.
> url:http://www.ureader.com/gp/1142-1.aspx
>