(on)mogelijke SQL query ?

[itsjohan]

tbv voorbeeld even versimpelde tabellen:

tblreizen
reis_ID
reis_datum
reis_naam
reis_bestemming

tblvervoer
vervoer_ID
vervoer_reis_ID
vervoer_naam

oftewel: tabelletje met reizen en een tabelletje met de vervoersopties van die reizen, tabellen hebben een 1:n relatie. De gewenste uitvoer is als volgt:

1 02-06-2002 superreis frankrijk (auto, trein, fiets)
2 03-07-2002 saaiereis duitsland (trein)
3 04-07-2002 nogeenreis belgie (fiets, trein)

Om deze uitvoer te produceren doe ik een select op de reistabel en vervolgens voor elk record (in ASP) een select op de vervoerstabel, vervolgens netjes vervoersopties op een rijtje zetten en klaar. Efficient is anders natuurlijk; bij een uitvoer van 30 records heb je 31 queries gedaan. Ik heb even zitten spelen met een GROUP BY query, maar dan vermenigvuldig je ook je uitvoer met het aantal vervoersmogelijkheden.... is er een SQL crack aanwezig die hier zijn licht over kan laten schijnen?? ik kan me niet ontrekken aan het idee dat dit toch wel mooier/beter moet kunnen Omgeving: SQL server en ASP.

[Skinny]

Je zou een stored procedure kunnen maken die alle vervoerssoorten retourneert op basis van een gegeven reis_id..

Pseudo code:

code:

function get_vervoer(reis_id) {

SELECT vervoer_naam 
FROM tblvervoer 
WHERE vervoer_reis_id = reis_id

Voeg alle velden samen met , ertussen en retourneer het resultaat

}

Query wordt dan zoiets als

code:

SELECT reis_ID, reis_datum reis_naam,
     reis_bestemming, get_vervoer(reis_ID) AS vervoer

[Verwijderd]

Op vrijdag 22 februari 2002 10:34 schreef itsjohan het volgende:
tbv voorbeeld even versimpelde tabellen:

tblreizen
reis_ID
reis_datum
reis_naam
reis_bestemming

tblvervoer
vervoer_ID
vervoer_reis_ID
vervoer_naam

oftewel: tabelletje met reizen en een tabelletje met de vervoersopties van die reizen, tabellen hebben een 1:n relatie. De gewenste uitvoer is als volgt:

1 02-06-2002 superreis frankrijk (auto, trein, fiets)
2 03-07-2002 saaiereis duitsland (trein)
3 04-07-2002 nogeenreis belgie (fiets, trein)

SELECT tblreizen.reis_ID, tblreizen.reis_datum, tblreizen.reis_naam, tblreizen.reis_bestemming, ISNULL(tblvervoer.vervoer_naam,'') AS vervoer_naam FROM tblreizen left join tblvervoer ON tblreizen.reis_ID = tblvervoer.reis_ID ORDER BY tblreizen.reis_ID

Dit levert 1 grote recordset op, die je in 1 keer doorloopt:
(uit het hoofd, dus kan fouten bevatten) (je moet het zelf maar goed formatteren met tables oid)

code:

' rsADO is recordset met de records.
iCurReisID=-1
iPrevReisID=-1
bBracketOpened=false
do while not rsADO.eof
    iPrevReisID=iCurReisID
    iCurReisID=rsADO("reis_ID").value
    if iPrevReisID<>iCurReisID then
         if bBracketOpened then
             %>
             )<BR />
             <%
         end if
         ' new trip, render
         %>
         <%=iCurReisID%> <%=rsADO("reis_datum").value%> <%=rsADO("reis_naam").value%> (
         <%
         bBracketOpened=true
    else
         ' vervoer
         %>
         <%=rsADO("vervoer_naam").value%>,
         <%
    end if
    rsADO.movenext
loop
if bBracketOpened then
     %>
     ) <BR />
     <%
end if

[itsjohan]

Skinny: ik begrijp je suggestie, ik zie alleen niet hoe ik die moet
realiseren; moet ik dan een 'extended stored procedure' bouwen ?
In een gewone sp kan je toch geen functie definieren ?
dan moet ik waarschijnlijk aan de c++ om een dll te maken en die in sql server in te voegen.
klopt dat ?

Otis: dank voor de code. Dit lijkt me al een stuk efficienter dan 31 queries.
Het blijft natuurlijk inefficient om een recordset die tot 10x groter is dan noodzakelijk naar
de asp-engine te sturen die er dan doorheen moet loopen om er een resultaat van de maken.

Maargoed; het lijkt er toch wel op alsof er geen mogelijkheid is binnen SQL om dit in 1 query te realiseren.
Je hebt dus toch altijd of een subquery nodig of je moet een mega-query maken die je vervolgens weer indikt.

Stel je maakt een query waarbij je beide tabellen joint, en vervolgens doe je een group by op reizen en een SUM(vervoer_naam),
dat zou nou leuk zijn... nou kan sql helaas alleen getallen optellen en geen strings,
maar dat zou wel precies zijn wat ik zou willen. Dan krijg je dus zoiets:

code:

SELECT tblreizen.reis_ID, tblreizen.reis_datum, tblreizen.reis_naam, tblreizen.reis_bestemming, SUM(tblvervoer.vervoer_naam) AS vervoersopties 
FROM tblreizen left join tblvervoer ON tblreizen.reis_ID = tblvervoer.reis_ID 
GROUP BY tblreizen.reis_ID, etc...