SQL -> full join op meerdere kolommen met dezelfde velden

[Aziona]

Hallo,
Ik heb een problem met een SQL-query waar ik niet uit kom. Ik heb twee tabellen.
Tabel X met kolom A, B, C en D alsmede Tabel Y met kolom E, F, G en H. De tabellen ABC en EFG bevatten dezelfde informatie. Nu moet er een full join gemaakt worden waarbij ik alleen de kolommen A, B, C, D, en H overhoudt.

Veld A bevat dezelfde informatie als veld E
Veld B bevat dezelfde informatie als veld F
Veld C bevat dezelfde informatie als veld G

De join moet gedaan worden op
ON
A= E AND
B=F AND
C=G

Als ik een RIGHT OUTER JOIN doe, dan worden de waarden in tabel Y zonder match niet meegenomen, doe ik een LEFT OUTER JOIN dan geschiedt hetzeflde maar dan voor de waarden in Tabel X zonder match. Dit is dus niet de bedoeling.

Als ik een FULL JOIN doe, dan moet ik volgens mij alle kolommen A, B, C, D, E, F G, en H selecteren, terwijl ik juist wil dat de waarden uit Tabel Y voor wat betreft de velden E, F en G in de kolommen A, B, C worden gezet voor zover er geen match is. Ik realiseer mij dat voor een aantal records in veld D geen waarde bevat, hetzelfde geld voor kolom H. Kolommen A, B, en C bevatten altijd een waarde om ABC met EFG ge-‘full’- joined moeten worden.

Het betreft hier een tabel met werkelijke uitgaven (Tabel X, veld D) en budgetbedragen (Tabel Y, veld H), waarbij de eerste drie velden bestaan uit Afdelingsnr (A en E), Accountnr (B en F), en Projectnr (C en G).


Ik wil een tabel overhouden met:
- Afdelingsnr
- Accountnr
- Projectnr
- Werkelijke uitgave
- Budgetbedrag

Een aantal records bevatten dus wel een Afdelingsnr, Account en eventueel een projectnr (niet alleen uitgaven zijn projectgebonden) maar geen Werkelijke uitgaven maar wel een budget. En sommige records zullen dus wel een budgetbedrag bevatten maar geen uitgaven. Ik heb al wel iets gebouwd maar de tabelnamen en veldnamen zijn zo complex dat het niet zal helpen om de query hier te publiceren.

[Dido]

Dit al geprobeerd? En waarom is het resultaat niet wat je verwcaht?

SQL:

select X.*, Y.h 
from Tabel1 X full outer join Tabel2 Y
on X.a = Y.e and X.b = Y.f and X.c = Y.g

edit: wacht, je wilt altijd sleutelwaarden.

Geen probleem, jij heb COALESCE nodig!

SQL:

select coalesce(X.a, Y.e) as Afd, coalesce(X.b, Y.f) as Acc, coalesce(X.c, Y.g) as Prj, X.d, Y.h 
from Tabel1 X full outer join Tabel2 Y
on X.a = Y.e and X.b = Y.f and X.c = Y.g

[ Voor 44% gewijzigd door Dido op 03-09-2009 14:48 ]

[Woy]

Je wilt dus een overzicht van de werkelijke kosten en het budget, van alle afdeling/account/project combinaties? Waarbij een van de twee kan ontbreken. ( voorbeel voor MS-SQL )

SQL:

SELECT COALESCE( x.AfdelingsNr, y.AfdelingsNr ), COALESCE( x.AccountNr, y.AccountNr ),COALESCE( x.ProjectNr, y.ProjectNr ), x.WerkelijkeUitgave, y.Budget
FROM Tabel1 x
FULL OUTER JOIN Tabel2 y
  ON x.AfdelingsNr = y.AfdelingsNr
  AND x.AccountNr = y.AccountNr
  AND x.ProjectNr = y.ProjectNr

Een andere oplossing is natuurlijk om gewoon je Afdelingen, Accounts en Projecten eerst te joinen, en daarop 2 left join's met deze tabellen.

edit:

Dido was me voor

[Aziona]

Jullie zijn wel erg goed hè|? Ik zou bijna willen vragen of je een rekening wilt sturen.

Ik heb het nog niet kunnen uitproberen omdat er gejoined moet worden met een tabel die op zichzelf weer een query is omdat deze tabel is gegroupen moet worden. De tabel met werkelijke uitgaven bevat namelijk alle transacties die eerst ge-grouped moeten worden naar maand (alle bedragen van de transacties moeten bij elkaar opgeteld worden op maandbasis. Deze query werkt maar als ik in plaats van deze tabel, de query opgeef, dus

OUTER JOIN ( query ) ipv OUTER JOIN tabel, dan krijg ik een error die ik nog niet opgelost heb. Mijn collega stelde voor om deze query naar een temporary tabel te schrijven met INTO en vervolgens te joinen met deze tijdelijke tabel. Dat ga ik morgen uitproberen maar ik vraag mij of of dit performace-wise de beste oplossing is. Waarschijnlijk maakt het niet uit omdat ik het Reporting Services rapport toch laat cachen.

[ Voor 1% gewijzigd door Aziona op 04-09-2009 07:01 . Reden: Typo ]

[Woy]

Kan je dan van die query niet gewoon een view maken?

[Aziona]

Reporting Services accepteerde geen 3 queries achter elkaar. Hij accepteerde de querird wel maar genereerde vervolgen geen fields list. Ik heb van de eerste twee queries inderdaad een view gemaakt. (erg simpel en handig overigens) en pas vervolgens de derde query op deze twee views toe. Dat werkte goed. Volgens moet het efficienter kunnen maar dat komt wel.

[DamadmOO]

Die fields list wordt wel vaker niet goed geupdate Maar in SSRS mag je inderdaad maar 1 query per dataset hebben. Of je in die query views of derived tables of weet ik veel wat gebruikt dat maakt dus niks uit. Verder heb ik in mijn SSRS project geen enkele query in SSRS gedefinieerd, maar alles staat gewoon in stored procedures. Dat komt deels omdat die dataset editor in SSRS zo brak is, maar de hoofdreden is omdat er een aantal rapporten zijn die precies dezelfde data gebruiken maar anders weergeven.

[Tubby]

Dit is toch gewoon een inner join?
(alleen de rijen die voorkomen in beide tabellen)

SQL:

select X.*, Y.h 
from Tabel1 X inner join Tabel2 Y
on X.a = Y.e and X.b = Y.f and X.c = Y.g

Owh, lamaar, je wil ook de rijen die in slechts 1 van beide tabellen voorkomen

Je zou kunnen left outer joinen en dan de resultaten van de rijen die alleen in tabel X voorkomen er achteraan plakken met union.

Verder zou je nog een cross join kunnen proberen:

SQL:

select X.*, Y.h 
from Tabel1 X,Tabel2 Y
on X.a = Y.e and X.b = Y.f and X.c = Y.g

Heb 'm nog nooit gebruikt, maar volgens mij zou ie het resultaat moeten geven wat je zoekt.

[ Voor 60% gewijzigd door Tubby op 04-09-2009 22:24 ]

[DamadmOO]

Tubby schreef op vrijdag 04 september 2009 @ 22:17:

...

Alle opties werken niet en dat terwijl de goede manier pas 2 keer vermeld was.

[Tubby]

DamadmOO schreef op vrijdag 04 september 2009 @ 22:26:
[...]

Alle opties werken niet en dat terwijl de goede manier pas 2 keer vermeld was.

De left outer join met een union er achteraan voor de missende resultaten uit tabel A werkt wel

[Aziona]

Die Coalesce was een prima oplossing dus ik ben als een kind zo blij met jullie!