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)
>----