Difficult SQL-JOIN/UNION-Problem

[lvpaul@gmx.net]

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.
aaapaul

[Simon Hayes]

Re: Difficult SQL-JOIN/UNION-Problem

This should work:

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

Simon

[lvpaul@gmx.net]

Re: Difficult SQL-JOIN/UNION-Problem

thanks simon.

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

select
coalesce(b.abdu rch,max(pl.abdu rch)) as abdurch,
coalesce(b.sieb dr,max(pl.siebd r)) as siebdr,
coalesce(b.prae ge,max(pl.praeg e)) as praege,
coalesce(b.etik ett,max(pl.etik ett)) as etikett,
sum(stueckvorja hr) as vorjahrstueck,s um(umsatzvorjah r) as
vorjahrumsatz,
sum(stueckheuer ) as heuerstueck,sum (umsatzheuer) as heuerumsatz,
max(pl.planstue ck) 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.sie bdr,b.praege,b. etikett

[Erland Sommarskog]

Re: Difficult SQL-JOIN/UNION-Problem

(lvpaul@gmx.net ) writes:[color=blue]
> I think, now it works correctly.
> Perhaps you can check it....
>
> select
> coalesce(b.abdu rch,max(pl.abdu rch)) as abdurch,
> coalesce(b.sieb dr,max(pl.siebd r)) as siebdr,
> coalesce(b.prae ge,max(pl.praeg e)) as praege,
> coalesce(b.etik ett,max(pl.etik ett)) as etikett,
> sum(stueckvorja hr) as vorjahrstueck,s um(umsatzvorjah r) as
> vorjahrumsatz,
> sum(stueckheuer ) as heuerstueck,sum (umsatzheuer) as heuerumsatz,
> max(pl.planstue ck) 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.sie bdr,b.praege,b. etikett[/color]

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.abdu rch, pl.abdurch) as abdurch,
coalesce(b.sieb dr, pl.siebdr) as siebdr,
coalesce(b.prae ge, pl.praege) as praege,
coalesce(b.etik ett, pl.etikett) as etikett,
pl.vorjahrstuec k, pl.vorjahrumsat z, 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(stueckvorja hr) as vorjahrstueck,
sum(umsatzvorja hr) as vorjahrumsatz,
sum(stueckheuer ) as heuerstueck,
sum(umsatzheuer ) as heuerumsatz,
max(pl.planstue ck) 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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[lvpaul@gmx.net]

Re: Difficult SQL-JOIN/UNION-Problem

Thanks Erland:

That´s it. It works fine.

select
coalesce(t1.abd urch,t2.abdurch ) as abdurch,
coalesce(t1.sie bdr,t2.siebdr) as siebdr,
coalesce(t1.pra ege,t2.praege) as praege,
coalesce(t1.eti kett,t2.etikett ) as etikett,
t1.planstueck,
t1.planwert,
t1.rfcq1,
t1.rfcq2,
t1.rfcq3,
t1.rfcq4,
t2.vorjahrstuec k,
t2.vorjahrumsat z,
t2.heuerstueck,
t2.heuerumsatz
from xplankundeext as t1
FULL JOIN
(
SELECT abdurch,siebdr, praege,etikett,
sum(stueckvorja hr) as vorjahrstueck,
sum(umsatzvorja hr) 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.

[Hugo Kornelis]

Re: Difficult SQL-JOIN/UNION-Problem

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

(snip)[color=blue]
>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 ??[/color]

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)

[lvpaul@gmx.net]

Re: Difficult SQL-JOIN/UNION-Problem

Thank You all !