Een op veel relaties zonder herhaling

23-08-2012 | Marcel van Langen | SQL Server | sql server, one, many, relatie, herhaling, rij, kolom

Stel: je hebt twee tabellen. In de ene tabel sla je plaatsen op, in de andere de postcodes die in deze plaats beschikbaar zijn. Het zal voorkomen dat een plaats meerdere postcodes heeft, maar een postcode hoort altijd maar bij een plaats. Een een op veel relatie dus. Hoe kun je nu in SQL Server realiseren dat je per plaats toch maar 1 rij terugkrijgt, waarbij in een van de kolommen alle postcodes staan vermeld?

Om het voorbeeld nog wat concreter te maken: stel dat de tabel Plaatsen 2 steden bevat: Ede en Wageningen. De tabel met Postcodes bevat 3 postcodes: 6705, 6715 en 6714. De laatste twee postcodes horen bij Ede, de eerste bij Wageningen. Een echte een-op-veel relatie dus. In de Postcode tabel zal logischerwijs een verwijzing staan naar de Plaatsen tabel. Wil je alle regels, dan zal de query er globaal als volgt uitzien:

SELECT
pla_id
, pla_plaats
, pos_id
, pos_postcode
FROM
postcode
LEFT OUTER JOIN plaats
ON postcode.pos_pla_id = plaats.pla_id
ORDER BY
pla_plaats ASC

In ons geval zal de bovenstaande query het volgende resultaat opleveren:

pla_id | pla_plaats | pos_id | pos_postcode
1 | Ede | 2 | 6715
2 | Ede | 3 | 6714
2 | Wageningen | 1 | 6705

Zoals je kunt zien komt Ede twee keer voor in de resultaten. Vaak zal dat prima zijn, maar wat nu als je per plaats een regel wilt, en dan in een apart veld een lijst met alle postcodes? Het volgende resultaat is dan gewenst:

plaats | postcodes
Ede | 6715, 6714
Wageningen | 6705

De oplossing wordt geboden door een unieke combinatie van twee functies binnen SQL Server, namelijk STUFF en XML Path. Kijk maar eens naar de volgende query die het gewenste resultaat oplevert:

SELECT
pla_plaats AS plaats
, Stuff((
SELECT ',' + pos_postcode
FROM postcode
WHERE postcode.pos_pla_id = pla_id
FOR XML Path('')
), 1, 1, '')
AS postcodes
FROM
plaats

Veel plezier met deze oplossing!

Over Morloff

Morloff ontwerpt en realiseert succesvolle webtoepassingen op het gebied van procesoptimalisatie, eCommerce en rapportage door de inzet van moderne webtechnieken, sociale media en zoekmachine optimalisatie.

over ons | diensten | portfolio | contact | route

naar website IJsmonster.nl naar website ICT intermediairs naar website marcelvanlangen.com

Twitter

Morloff

Cuneraweg 169a
3911 RH Rhenen

M +31 (0)6 - 515 96 189
W www.morloff.nl
E contactformulier

Morloff op Twitter Morloff op Facebook Morloff op LinkedIn Morloff via RSS