[SQL] Join onder een voorwaarde

[gizmo9003]

Ik weet niet echt een correcte titel te bedenken, maar ik zal mijn probleem omschrijven. In mijn database heb ik 2 tabellen, persoonsgegevens en adresgegevens. Aan elkaar gekoppeld via de id's.

Nu wil ik alle personen selecteren, met bijbehorend adres, geen probleem, een simpele join lost dat op. Maar in mijn tabel adresgegevens, zit een kolom "correspondentie", die 0 of 1 is.

Nu wil ik dat hij alle personen weergeeft, met bij ieder persoon het adres waarbij correspondentie op "1" staat (als die tenminste in de tabel staat), en anders gewoon het adres waar "0" staat bij correspondentie.

Dus mijn query moet niet zomaar het adres van een persoon weergeven, maar er zit een rangorde in. Als er een adres bestaat met correspondentie "1", moet die weergegeven worden, anders moet die met correspondentie "0" weergegeven worden.

De query die ik tot nu toe heb is:

code:

SELECT * FROM persoonsgegevens RIGHT JOIN adresgegevens ON persoonsgegevens.ID = adresgegevens.ID

Maar ik weet dus niet hoe ik dat laatste stuk moet implementeren. Heb wat geprobeerd met WHERE's, maar dat lukt niet helemaal.

[mocean]

Kan vast efficienter:

code:

SELECT * FROM persoonsgegevens 
   RIGHT JOIN adresgegevens ON persoonsgegevens.ID = adresgegevens.ID 
WHERE adresgegevens.correspondentie=1
UNION 
SELECT * FROM persoonsgegevens 
   RIGHT JOIN adresgegevens ON persoonsgegevens.ID = adresgegevens.ID 
WHERE adresgegevens.correspondentie=0 
      AND persoonsgegevens.id NOT IN 
 (SELECT id FROM persoonsgegevens 
   RIGHT JOIN adresgegevens ON persoonsgegevens.ID = adresgegevens.ID WHERE adresgegevens.correspondentie=1)

[xos]

Dit is op verschillende manieren op te lossen, een voorbeeld:

SQL:

select p.id, p.naam, a2.correspondentie, a2.straat from persoonsgegevens p
inner join ( select id, max( correspondentie ) as correspondentie from adresgegevens group by id ) a1 on a1.id = p.id
inner join ( select id, correspondentie, straat from adresgegevens ) a2 on a2.id = a1.id and a2.correspondentie = a1.correspondentie

Eventueel kan je ook nog voor een constructie met having of subqueries gaan.

[RobIII]

Over welk SQL Dialect hebben we het hier? MySQL? MSSQL? Oracle?

SQL:

SELECT persoonsgegevens.id as pid, persoonsgegevens.naam, a1.id as aid, a1.straat
FROM persoonsgegevens
INNER JOIN adresgegevens a1 ON persoonsgegevens.id = a1.persoonid
INNER JOIN adresgegevens a2 ON persoonsgegevens.id = a2.persoonid
GROUP BY persoonsgegevens.id, persoonsgegevens.naam, a1.id, a1.straat, a1.correspondentie
HAVING a1.correspondentie = MAX(a2.correspondentie)
ORDER BY persoonsgegevens.id

Zoiets.

code:

TABEL: persoonsgegevens         TABEL: adresgegevens
=======================         ====================
id     naam                     id correspondentie persoonid straat
-----------------------         -------------------------------------
1      Jan                      1  0               2         straat A
2      Klaas                    2  0               1         straat B
3      Piet                     3  1               3         straat C
4      Puk                      4  1               2         straat D
5      Tweaky                   5  0               4         straat E
                                6  0               3         straat F
                                7  0               5         straat G

OUTPUT:
=======
pid    naam  aid  straat
1      Jan    2   Straat B
2      Klaas  4   Straat D
3      Piet   3   Straat C
4      Puk    5   Straat E
5      Tweaky 7   Straat G

Edwardvb schreef op woensdag 14 november 2007 @ 00:16:

mocean schreef op dinsdag 13 november 2007 @ 21:36:

Die werkt alleen als je correspondentie 0 of 1 hebt; misschien dat een 2, 3 etc. ook nog mogelijk is? (Ik zeg dat omdat het 'principe' achter dit probleem vaker voorkomt en dan heb je vaak meer dan alleen 0 en 1) En ook als dat niet het geval is worden in mijn query geen 'magic numbers' gebruikt

xos schreef op dinsdag 13 november 2007 @ 22:52:

Met jouw query klopt op mijn testdata de output niet
Ik heb eens even de query-plans naast elkaar gelegd; trek je eigen conclusies TS

mocean	xos	RobIII	Edwardvb

Let niet op de gare thumbs

[ Voor 226% gewijzigd door RobIII op 14-11-2007 01:00 ]

[P.O. Box]

dit is bv. ook een optie: je kunt meerdere voorwaarden bij een join doen en dan via een coalesce de waarde krijgen...

dus zoiets:

SQL:

select persoon.naam, coalesce(adresA.straat, adresB.straat)
from
persoon
left join
adres adresA on (persoon.id = adres.persoonid and correspondentie=1)
left join
adres adresB on (persoon.id = adres.persoonid and correspondentie=0)

als correspondentie = 1 voor een persoon niet bestaat zal adresA.straat NULL zijn, dus pakt hij adresB.straat...

[EfBe]

zoiets?

code:

SELECT  * 
FROM    persoonsgegevens p
LEFT JOIN (
    SELECT TOP 1 * 
    FROM    adresgegevens a
    WHERE   a.ID = p.ID
    ORDER BY correspondentie DESC
) adres
on p.ID = adres.ID

[P.O. Box]

RobIII schreef op woensdag 14 november 2007 @ 00:10:
Die werkt alleen als je correspondentie 0 of 1 hebt; misschien dat een 2, 3 etc. ook nog mogelijk is?

Klopt... maar het lijkt me dat in dit geval een adres een correspendentieadres is of niet... maar idd kunnen er meerdere soorten adressen zijn... alleen gaat de MAX dan ook de mist in als je correspondentienummers hebt die groter dan 1 zijn... (nou is dat met een where wel op te lossen natuurlijk)

[The Eagle]

Is gewoon een WHERE EXISTS gebruiken geen optie

code:

SELECT * from persoon a, adres b
where [persoon where clause]
and EXISTS (select 'x' from adres b1 [where-clause] and corrspondentie = '1')

[EfBe]

The Eagle schreef op woensdag 14 november 2007 @ 09:19:
Is gewoon een WHERE EXISTS gebruiken geen optie

code:

SELECT * from persoon a, adres b
where [persoon where clause]
and EXISTS (select 'x' from adres b1 [where-clause] and corrspondentie = '1')

Die selecteert niet het adres met correspondentie 0 wanneer er geen correspondentie 1 is. Dus: indien beide: kies 1, anders kies 0. Typische top 1 query joined met persoon.

Overigens is RIGHT JOIN fout in de topicstart, je wilt alle personen weergeven, dan moet je joinen naar persoon toe, dus left, en niet right.

[gizmo9003]

RobIII schreef op woensdag 14 november 2007 @ 00:10:
...

Bedankt, ik heb deze implementatie gebruikt en die werkt prima! Hoewel het niet de bedoeling is dat er nog andere getallen dan 0 en 1 voorkomen, heb ik hem toch maar gebruikt. Wie weet wat ik later nog ga veranderen!

[xos]

RobIII schreef op woensdag 14 november 2007 @ 00:10:
Over welk SQL Dialect hebben we het hier? MySQL? MSSQL? Oracle?

SQL:

SELECT persoonsgegevens.id as pid, persoonsgegevens.naam, a1.id as aid, a1.straat
FROM persoonsgegevens
INNER JOIN adresgegevens a1 ON persoonsgegevens.id = a1.persoonid
INNER JOIN adresgegevens a2 ON persoonsgegevens.id = a2.persoonid
GROUP BY persoonsgegevens.id, persoonsgegevens.naam, a1.id, a1.straat, a1.correspondentie
HAVING a1.correspondentie = MAX(a2.correspondentie)
ORDER BY persoonsgegevens.id

<knip>

Mja, mijn query werkt idd niet zo best met jouw gekozen kolomnamen. Verander de ID => persoonid (adresgegevens tbl) in mijn query en het gaat al een stuk beter

Op welke database heb jij deze exectionplans gemaakt? Op mijn postgresql db kom ik tot behoorlijk andere resultaten?

[RobIII]

xos schreef op woensdag 14 november 2007 @ 22:57:
Mja, mijn query werkt idd niet zo best met jouw gekozen kolomnamen. Verander de ID => persoonid (adresgegevens tbl) in mijn query en het gaat al een stuk beter

Ergens heb ik een fout gemaakt bij het 'vertalen' van je query en ben ik in de war geraakt met de id's; hij doet het inderdaad wel. Je executionplan ziet er dan zo uit:

xos schreef op woensdag 14 november 2007 @ 22:57:
Op welke database heb jij deze exectionplans gemaakt? Op mijn postgresql db kom ik tot behoorlijk andere resultaten?

MSSQL 2000. Ik heb ook wel 2005 tot mijn beschikking (en evt. MySQL) maar die had/heb ik niet zo snel bij de hand.

[ Voor 5% gewijzigd door RobIII op 15-11-2007 01:13 ]

[EfBe]

Nofi, maar volgens mij is mijn query stukken efficienter dan die groupby.

[xos]

nevermind...

[ Voor 91% gewijzigd door xos op 15-11-2007 17:32 ]

[Verwijderd]

Hoe zou je dit op oracle 8i en versie daarboven moeten doen? Oracle 8i kent geen INNER of LEFT JOIN als operator.

[whoami]

Zoek eens in de documentatie hoe je in Oracle een OUTER JOIN kunt doen. Maw, zoek eens op het trefwoord OUTER JOIN.
Ik dacht dat je dit met een (+) kon aangeven.

[Verwijderd]

Als ik queries van hierboven ombouw met de JOIN syntax zoals deze in 8i geldt, inderdaad met de +, kreeg ik de melding dat ik geen geldige GROUP BY clausule gebruikte. Ik verwacht dat ik meer moet wijzigen om de HAVING te ondersteunen. De andere query gebruikt een LEFT JOIN die ik ook niet omgezet krijg.

[bigbeng]

Laat je omgezette query eens zien.

[Verwijderd]

SELECT tpersoon.pers_nr,
tpersoon.naam_samen,
tcontract1.object_id
FROM dpib010 tpersoon,
dpic300 tcontract1,
dpic300 tcontract2
WHERE tpersoon.pers_nr = tcontract1.pers_nr
AND tpersoon.pers_nr = tcontract2.pers_nr
GROUP BY tpersoon.pers_nr, tpersoon.naam_samen, tcontract1.object_id
HAVING tcontract1.uitdnst_dt = MAX(tcontract2.uitdnst_dt)
ORDER BY tpersoon.pers_nr

foutmelding = ORA-00979: not a GROUP BY expression

ah damn ik heb hem al gevonden. dank voor de reacties.

[ Voor 12% gewijzigd door Verwijderd op 14-01-2008 14:49 ]

[bigbeng]

Ik wil je er wel even op wijzen dat de gegeven query geen outer join bevat.