[sql] kolom meerdere keren in select-clause bij 1 op n

[JeroenTheStig]

Situatie:
Ik heb twee tabellen, die een 1 op n relatie hebben:
naw(id, naam, adres, woonplaats)
order(id, naw_id, omschrijving)

Voor de eenvoud heb ik de tabellen flink vereenvoudigd en de tabel- en kolomnamen zijn fictief.

Probleem:
De klant wil graag op korte termijn enkele rapportages hebben. In plaats van het bouwen van een compleet nieuwe report, doen we dit daarom in de vorm van een puntkommagescheiden ascii-bestand, dat door een stuk code wordt gegenereerd. Dit bestand kan onder andere eenvoudig in ms excel worden gebruikt. De input is uiteraard een sql-query en enkele inputvariabelen zodat de gebruiker een bepaalde selectie kan maken. Een tekortkoming van de code die wordt gebruikt om deze bestanden te genereren is dat het (nog) geen master-detail rapportages kan genereren.

Ik wil nu een rapportage hebben waarbij de selectie per record de naw-gegevens bevat, inclusief de eerste 4 orderomschrijvingen. (het klinkt onlogisch en lelijk, maar de klant wil het nu eenmaal zo)
Dit ziet er dus als volgt uit:

code:

naam    adres             woonplaats    order1    order2    order3    order4
pietje  hoofdstraat 11    amsterdam     blabla1   blablab2  blabla3   blabla4
klaas   de dam 10         amsterdam     blaat     burp      barf

Uiteraard zijn order1 tm order4 aparte records uit tabel order. Nu is mijn vraag hoe ik met goed fatsoen een sql-query kan maken die dit voor elkaar kan krijgen. Er wordt namelijk in de select-clause 4 keer iets met de kolom order.omschrijving gedaan. Is dit uberhaupt oplosbaar, of moet dit alsnog een master-detail rapportage worden (wat we vanwege de implementatie van de code liever niet willen hebben)

[ Voor 3% gewijzigd door JeroenTheStig op 23-08-2005 20:45 ]

[BCC]

Subquery. Eerst query je alle NAW's, dan knoop je daar met subqueries elke keer de TOP 4 orders aan?

[ Voor 23% gewijzigd door BCC op 23-08-2005 20:49 ]

[JeroenTheStig]

BCC schreef op dinsdag 23 augustus 2005 @ 20:48:
Subquery. Eerst query je alle NAW's, dan knoop je daar met subqueries elke keer de TOP 4 orders aan?

Ik ben nog niet zo geavanceerd in het bouwen van queries, maar wat jij bedoelt is iets als dit?
[code]
select
naw.naam,
naw.adres,
naw.woonplaats,
(select order.omschrijving from order where id=0) as "order1",
(select order.omschrijving from order where id=1) as "order2",
(select order.omschrijving from order where id=2) as "order3",
(select order.omschrijving from order where id=3) as "order4"
from naw, order;

Als dit is wat je bedoelt, dan krijg ik alsnog problemen met het id-nummer, want het is ook goed mogelijk dat id 1 niet bestaat.

[MaTriCX]

SQL:

SELECT 
   naw.naam, 
   naw.adres, 
   naw.woonplaats, 
   oa.omschrijving, 
   ob.omschrijving, 
   oc.omschrijving, 
   od.omschrijving 
FROM naw 
LEFT JOIN order oa on naw.nav_id = oa.nav_id 
LEFT JOIN order ob on naw.nav_id = ob.nav_id
LEFT JOIN order oc on naw.nav_id = oc.nav_id
LEFT JOIN order od on naw.nav_id = od.nav_id

Ziet er misschien lelijk uit, maar dit zou moeten werken. Dat is, als ik je goed heb begrepen

[edit]
Waarschijnlijk toch niet helemaal goed begrepen

[ Voor 11% gewijzigd door MaTriCX op 24-08-2005 19:08 ]

[r0bert]

Volgens mij heeft ie maar 1 tabel met orders, dus dat maakt het simpeler lijkt me. En volgens mij moet er nog even ergens een limit 4 dan in komen. Moet de TS toch al een heel eind komen. ?

[P_de_B]

Het eerste wat we eigenlijk moeten weten is wat zijn de 1e vier orders? Oplopen gesorteerd op ID, of op een datumveld, en welke database gebruik je?

[Verwijderd]

P_de_B schreef op woensdag 24 augustus 2005 @ 08:21:
Het eerste wat we eigenlijk moeten weten is wat zijn de 1e vier orders? Oplopen gesorteerd op ID, of op een datumveld, en welke database gebruik je?

Idd de volgorde is belangrijk. Bovendien, voor het geval dat klanten minder dan 4 orders heeft, is het handig om outer joins te gebruiken.

Ik trouwens dit probleem heel anders benaderen en het tekst bestand met bijv. een simpele perl script genereren.

[JeroenTheStig]

P_de_B schreef op woensdag 24 augustus 2005 @ 08:21:
Het eerste wat we eigenlijk moeten weten is wat zijn de 1e vier orders? Oplopen gesorteerd op ID, of op een datumveld, en welke database gebruik je?

Wat mij betreft worden de klanten met de 4 laagste id's weergegeven, het gaat namelijk even om het voorbeeld. Het gaat om een Oracle database, maar ik denk niet dat dit veel verschil maakt als het om de sql-query gaat.

MaTriCX schreef op woensdag 24 augustus 2005 @ 00:26:

code:

SELECT naw.naam, naw.adres, naw.woonplaats, oa.omschrijving, ob.omschrijving, oc.omschrijving, od.omschrijving FROM naw 
LEFT JOIN order oa on naw.nav_id = oa.nav_id 
LEFT JOIN order ob on naw.nav_id = ob.nav_id
LEFT JOIN order oc on naw.nav_id = oc.nav_id
LEFT JOIN order od on naw.nav_id = od.nav_id

Ziet er misschien lelijk uit, maar dit zou moeten werken. Dat is, als ik je goed heb begrepen

Ik heb geprobeerd je code te begrijpen en te testen, maar ik zie het nog niet helemaal. Hoe kom je aan de oa, ob, oc en od? Dit zijn uiteraard labels Maar dan nog kom ik er niet uit

Verwijderd schreef op woensdag 24 augustus 2005 @ 09:03:
[...]


Idd de volgorde is belangrijk. Bovendien, voor het geval dat klanten minder dan 4 orders heeft, is het handig om outer joins te gebruiken.

Ik trouwens dit probleem heel anders benaderen en het tekst bestand met bijv. een simpele perl script genereren.

Er moet inderdaad rekening mee worden gehouden dat een klant minder dan 4 orders kan hebben. Het veld 'order4' hoort dan gewoon leeg te zijn.

Daarnaast is het geen optie om dit op te lossen met een simpel scriptje, aangezien de code alleen een sql-query als input accepteert.

[ Voor 26% gewijzigd door JeroenTheStig op 24-08-2005 09:58 ]

[ACM]

Boktor schreef op woensdag 24 augustus 2005 @ 09:54:
Wat mij betreft worden de klanten met de 4 laagste id's weergegeven, het gaat namelijk even om het voorbeeld. Het gaat om een Oracle database, maar ik denk niet dat dit veel verschil maakt als het om de sql-query gaat.
[knip]
Daarnaast is het geen optie om dit op te lossen met een simpel scriptje, aangezien de code alleen een sql-query als input accepteert.

Vind je dan dit niet typisch iets om in een stored procedure te verwerken?

[P_de_B]

SQL:

SELECT 
    naw.Naam, naw.woonplaats,
    (SELECT  omschrijving FROM orders WHERE rownum = 1 AND naw_id = naw.id ORDER BY Id) as Order1,
    (SELECT  omschrijving FROM orders WHERE rownum = 2 AND naw_id = naw.id ORDER BY Id) as Order2,
    (SELECT  omschrijving FROM orders WHERE rownum = 3 AND naw_id = naw.id ORDER BY Id) as Order3,
    (SELECT  omschrijving FROM orders WHERE rownum = 4 AND naw_id = naw.id ORDER BY Id) as Order4
FROM naw

Dit zou moeten werken als ik de rownum functie goed begrijp.

[Verwijderd]

Getest met Oracle 8i.

SQL:

SELECT N.NAAM
,      N.ADRES
,      N.WOONPLAATS
,      O1.OMSCHRIJVING ORDER1
,      O2.OMSCHRIJVING ORDER2
,      O3.OMSCHRIJVING ORDER3
,      O4.OMSCHRIJVING ORDER4
FROM   NAW N
,      ( -- subquery voor order 1
   SELECT  O.NAW_ID
   ,       O.OMSCHRIJVING
   FROM    NAW    N
   ,       ORDERS O
   WHERE  (O.ID, 1) IN (
         SELECT ID
         ,      ROWNUM NUM
         FROM   ( -- dit is nodig om op ID te sorteren
            SELECT *
            FROM   ORDERS
            ORDER  BY ID
         )
         WHERE  NAW_ID = N.ID
   )
) O1
,      (
   SELECT  O.NAW_ID
   ,       O.OMSCHRIJVING
   FROM    NAW    N
   ,       ORDERS O
   WHERE  (O.ID, 2) IN (
         SELECT ID
         ,      ROWNUM NUM
         FROM   (
            SELECT *
            FROM   ORDERS
            ORDER  BY ID
         )
         WHERE  NAW_ID = N.ID
   )
) O2
,      (
   SELECT  O.NAW_ID
   ,       O.OMSCHRIJVING
   FROM    NAW    N
   ,       ORDERS O
   WHERE  (O.ID, 3) IN (
         SELECT ID
         ,      ROWNUM NUM
         FROM   (
            SELECT *
            FROM   ORDERS
            ORDER  BY ID
         )
         WHERE  NAW_ID = N.ID
   )
) O3
,      (
   SELECT  O.NAW_ID
   ,       O.OMSCHRIJVING
   FROM    NAW    N
   ,       ORDERS O
   WHERE  (O.ID, 4) IN (
         SELECT ID
         ,      ROWNUM NUM
         FROM   (
            SELECT *
            FROM   ORDERS
            ORDER  BY ID
         )
         WHERE  NAW_ID = N.ID
   )
) O4
WHERE  N.ID = O1.NAW_ID (+)
AND    N.ID = O2.NAW_ID (+)
AND    N.ID = O3.NAW_ID (+)
AND    N.ID = O4.NAW_ID (+)

[JeroenTheStig]

P_de_B schreef op woensdag 24 augustus 2005 @ 11:04:

SQL:

SELECT 
    naw.Naam, naw.woonplaats,
    (SELECT  omschrijving FROM orders WHERE rownum = 1 AND naw_id = naw.id ORDER BY Id) as Order1,
    (SELECT  omschrijving FROM orders WHERE rownum = 2 AND naw_id = naw.id ORDER BY Id) as Order2,
    (SELECT  omschrijving FROM orders WHERE rownum = 3 AND naw_id = naw.id ORDER BY Id) as Order3,
    (SELECT  omschrijving FROM orders WHERE rownum = 4 AND naw_id = naw.id ORDER BY Id) as Order4
FROM naw

Op de 1 of andere manier werkt de query

code:

SELECT  omschrijving FROM orders WHERE rownum = 1 AND naw_id = naw.id ORDER BY Id

wel op zichzelf, maar niet in jouw query. Ik ben er dus ook nog niet uit hoe ik jouw code aan de praat kan krijgen.

Verwijderd schreef op woensdag 24 augustus 2005 @ 12:51:
Getest met Oracle 8i.

SQL:

...

Hallo zeg, wat een lap
Maar het werkt wel Nu alleen nog kijken of ik het kan begrijpen. thanx!

[ Voor 46% gewijzigd door JeroenTheStig op 24-08-2005 13:13 ]