Difficult SQL-JOIN/UNION-Problem

Hallo !

I have 2 tables with similar construction.

DataOLD and DataNEW.

In my grid I need a JOINED table.

On the one hand I need all the data from the old table and the data
from then new table with the same id (left outer join works)

But additionally I need all rows from the new table where no equivalent
rows are in the old table.

example:
TableOLD
diameter amountOLD
20 100
50 200
30 300

TableNEW
diameter amountNEW
20 500
60 600
70 120

Wished Result:
diameter amountOLD amoutNEW
20 100 500
50 200 NULL
30 300 NULL
60 NULL 600
70 NULL 120

Who can help me?

Many Thanks.
aaapaulThis should work:

select
coalesce(o.diameter, n.diameter),
o.amount,
n.amount
from
dbo.old o
full outer join dbo.new n
on o.diameter = n.diameter

Simon|||thanks simon.

I think, now it works correctly.
Perhaps you can check it...

select
coalesce(b.abdurch,max(pl.abdurch)) as abdurch,
coalesce(b.siebdr,max(pl.siebdr)) as siebdr,
coalesce(b.praege,max(pl.praege)) as praege,
coalesce(b.etikett,max(pl.etikett)) as etikett,
sum(stueckvorjahr) as vorjahrstueck,sum(umsatzvorjahr) as
vorjahrumsatz,
sum(stueckheuer) as heuerstueck,sum(umsatzheuer) as heuerumsatz,
max(pl.planstueck) as planstueck,max(planwert) as planwert, max(rfcq1)
as rfcq1, max(rfcq2) as rfcq2, max(rfcq3) as rfcq3, max(rfcq4) as rfcq4
from budgetplan b
full outer join xplankundeext pl
on b.abdurch = pl.abdurch and
b.siebdr = pl.siebdr and
b.praege = pl.praege and
b.etikett = pl.etikett
where (b.kdnr = '11461' or pl.kdnr = '11461')
group by b.abdurch,b.siebdr,b.praege,b.etikett|||(lvpaul@.gmx.net) writes:
> I think, now it works correctly.
> Perhaps you can check it...
> select
> coalesce(b.abdurch,max(pl.abdurch)) as abdurch,
> coalesce(b.siebdr,max(pl.siebdr)) as siebdr,
> coalesce(b.praege,max(pl.praege)) as praege,
> coalesce(b.etikett,max(pl.etikett)) as etikett,
> sum(stueckvorjahr) as vorjahrstueck,sum(umsatzvorjahr) as
> vorjahrumsatz,
> sum(stueckheuer) as heuerstueck,sum(umsatzheuer) as heuerumsatz,
> max(pl.planstueck) as planstueck,max(planwert) as planwert, max(rfcq1)
> as rfcq1, max(rfcq2) as rfcq2, max(rfcq3) as rfcq3, max(rfcq4) as rfcq4
> from budgetplan b
> full outer join xplankundeext pl
> on b.abdurch = pl.abdurch and
> b.siebdr = pl.siebdr and
> b.praege = pl.praege and
> b.etikett = pl.etikett
> where (b.kdnr = '11461' or pl.kdnr = '11461')
> group by b.abdurch,b.siebdr,b.praege,b.etikett

That's a bit of a mouthfull to ask people who don't know your data model
or your business to review your code.

...but it looks funny with the GROUP BY and all the MAX. Here is a
possible rewrite, where I am pre-aggregating in a derived table. But
it's a based on a lot of guesswork, so it may produce a whole bunch of
crap.

select coalesce(b.abdurch, pl.abdurch) as abdurch,
coalesce(b.siebdr, pl.siebdr) as siebdr,
coalesce(b.praege, pl.praege) as praege,
coalesce(b.etikett, pl.etikett) as etikett,
pl.vorjahrstueck, pl.vorjahrumsatz, pl.heuerstueck,
pl.heuerumsatz, pl.planstueck,
b.planwert, b.rfcq1, b.rfcq2, b.rfcq3, b.rfcq4
from budgetplan b
full join (SELECT abdurch, siebdr, praege, etikett,
sum(stueckvorjahr) as vorjahrstueck,
sum(umsatzvorjahr) as vorjahrumsatz,
sum(stueckheuer) as heuerstueck,
sum(umsatzheuer) as heuerumsatz,
max(pl.planstueck) as planstueck,
FROM xplankundeext
WHERE kdnr = '11461'
GROUP BY abdurch, siebdr, praege, etikett) AS pl
on b.abdurch = pl.abdurch and
b.siebdr = pl.siebdr and
b.praege = pl.praege and
b.etikett = pl.etikett
where (b.kdnr = '11461' or pl.kdnr = IS NULL)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland:

Thats it. It works fine.

select
coalesce(t1.abdurch,t2.abdurch) as abdurch,
coalesce(t1.siebdr,t2.siebdr) as siebdr,
coalesce(t1.praege,t2.praege) as praege,
coalesce(t1.etikett,t2.etikett) as etikett,
t1.planstueck,
t1.planwert,
t1.rfcq1,
t1.rfcq2,
t1.rfcq3,
t1.rfcq4,
t2.vorjahrstueck,
t2.vorjahrumsatz,
t2.heuerstueck,
t2.heuerumsatz
from xplankundeext as t1
FULL JOIN
(
SELECT abdurch,siebdr,praege,etikett,
sum(stueckvorjahr) as vorjahrstueck,
sum(umsatzvorjahr) as vorjahrumsatz,
sum(stueckheuer) as heuerstueck,
sum(umsatzheuer) as heuerumsatz
FROM Budgetplan b left outer join dim_pcenter p1 on b.wgnr = p1.wgnr
WHERE b.kdnr = '11461' and p1.kstnr = 460
GROUP BY abdurch,siebdr,praege,etikett
) as t2
ON
t1.abdurch = t2.abdurch and
t1.siebdr = t2.siebdr and
t1.praege = t2.praege and
t1.etikett = t2.etikett
WHERE t1.kdnr = '11461' and t1.kstnr = 460

One Question at last:

Is it possible to join 2 Tables together where each table is grouped by
?

(Select a
from table tabA
group by a
) as t1
join
(Select b
from table tabB
group by b
) as t2
on t1.xy = t2.xy ??

Thanks.|||On 5 Aug 2005 04:06:20 -0700, lvpaul@.gmx.net wrote:

(snip)
>One Question at last:
>Is it possible to join 2 Tables together where each table is grouped by
>?
>(Select a
>from table tabA
>group by a
>) as t1
>join
>(Select b
>from table tabB
>group by b
>) as t2
>on t1.xy = t2.xy ??

Hi lvpaul,

Quite easy to test for yourself, isn't it?

The answer, BTW, is yes. You can use a non-correlated subquery anywhere
you can specify a tablename. Check out "derived tables" in Books Online.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank You all !