[SQL Server 2000] Union All alternatief? *

[Mesjefskie]

Excuses, vergeten titel goed in te vullen! Moet zijn: [SQL Server 2000] Is er een goed Union All alternatief?

Hallo,

Ik zit met het volgende probleem: op mijn werk wordt gebruik gemaakt van 3 tabellen waar informatie over recepten in opgeslagen staat. In de eerste tabel, Recipe Headers staat de naam van het product dat met dit recept gemaakt kan worden maar ook een ID dat verwijst naar Recipe Management waar de grondstofcode's van dat recept staan. Verder is er dan nog een tabel Grondstoffen waar grondstofcode's aan een naam en een doseerplaats gekoppeld worden.
De Recipe Headers tabel ziet er zo uit:

code:

Kolomen / Voorbeeldwaarde
ID / 1973
Recipe Code / 6760
Recipe Name / "lekkere koekjes"
...

De Recipe Management tabel ziet er zo uit:

code:

Kolomen / Voorbeeldwaarde
ID /1973
Code 01 / M2100
Code 02 / M2104
...
Code 100 / M2001
Procent 01 / 12
Procent 02 /16
...
Procent 100 / 0.212

De Gronstof tabel ziet er zo uit:

code:

Kolommen / Voorbeeldwaarde
Code / M2100
Description / bloem
Dosing Area / D1
...

Mijn vraag is nu: hoe kan ik op een nette manier een query of stored procedure maken die de volgende vraag 'beantwoord': Ik wil van recept <variabel> alle grondstofnamen en doseerplaats weten. Iets wat dus ongeveer het volgende resultaat geeft:

code:

grondstof - omschrijving - doseerplaats
M2100 - bloem - D1
M2104 - suiker - D1
L1901 - water - L1
...

Ik heb al het een ander geprobeerd maar het probleem, voor mij dan toch, ligt in het feit dat de Recipe Management tabel per rij 201 kolommen heeft. 1 ID kolom, 100 grondstof kolommen en 100 percentage kolommen. (Is indertijd zo opgezet...)

Als ik hier bijvoorbeeld UNION All met een left outer join gebruik zou ik dat 100 keer moeten herhalen...

SQL:

SELECT      [Recipe Management].[Coda 01] AS grondstof, 
            [Recipe Management].[Procent 01] AS procent, 
            Grondstof.Description AS omschrijving, 
            Grondstof.[Dosing Area] AS doseerplaats
FROM        [Recipe Management] LEFT OUTER JOIN
                Grondstof ON [Recipe Management].[Coda 01] = Grondstof.Coda
WHERE     ([Recipe Management].ID = 2155)
UNION ALL
SELECT      [Recipe Management_1].[Coda 02] AS grondstof, 
            [Recipe Management_1].[Procent 02] AS procent, 
            Grondstof_1.Description AS omschrijving, 
            Grondstof_1.[Dosing Area] AS doseerplaats
FROM         [Recipe Management] AS [Recipe Management_1] LEFT OUTER JOIN
                Grondstof AS Grondstof_1 ON [Recipe Management_1].[Coda 02] = Grondstof_1.Coda
WHERE     ([Recipe Management_1].ID = 2155)
(...)
UNION ALL
SELECT      [Recipe Management_1].[Coda 100] AS grondstof, 
            [Recipe Management_1].[Procent 100] AS procent, 
            Grondstof_1.Description AS omschrijving, 
            Grondstof_1.[Dosing Area] AS doseerplaats
FROM         [Recipe Management] AS [Recipe Management_1] LEFT OUTER JOIN
                Grondstof AS Grondstof_1 ON [Recipe Management_1].[Coda 100] = Grondstof_1.Coda
WHERE     ([Recipe Management_1].ID = 2155)

Het kan werken maar ik ben er bijna zeker van dat er een andere oplossing voor moet zijn. (De left join is nodig omdat sommige kolommen in de Recipe Management tabel M0000 bevatten. Dit is geen grondstof die in de Grondstoffen tabel staat maar slechts 'opvulsel'. Toch moeten ook die records worden weergegeven...)
Kan iemand mij in de goeie richting wijzen?

[ Voor 1% gewijzigd door Mesjefskie op 18-01-2008 17:09 . Reden: Titel vergeten, excuus! ]

[Verwijderd]

Mesjefskie schreef op vrijdag 18 januari 2008 @ 16:56:
Kan iemand mij in de goeie richting wijzen?

Dus als ik het goed begrijp heb je een tabel 'Recipe Management' met een 'ID', 100 velden voor 'Code' en 100 velden voor 'Procent'?

Een goede richting zou zijn om een tabel te maken met 1 veld voor 'Code' en 1 voor 'Procent' en daar meerdere rijen instoppen. Heb je ook direct je limiet van 100 codes per recept opgelost...

ps. Misschien even zoeken via google naar normaliseren?

[ Voor 6% gewijzigd door Verwijderd op 18-01-2008 17:08 ]

[Mesjefskie]

Verwijderd schreef op vrijdag 18 januari 2008 @ 17:06:
[...]

Dus als ik het goed begrijp heb je een tabel 'Recipe Management' met een 'ID', 100 velden voor 'Code' en 100 velden voor 'Procent'?

De 'goede' richting zou zijn om een tabel te maken met 1 veld voor 'Code' en 1 voor 'Procent' en daar meerdere rijen instoppen. Heb je ook direct je limiet van 100 codes per recept opgelost...

Je hebt natuurlijk helemaal gelijk maar deze tabellen zitten zodanig verweven in de bestaande structuur dat dit niet 'even' aan te passen is. Ik heb al wel zitten denken om met tijdelijke tabellen te gaan werken maar die zal je dan toch ook eerst moeten vullen met een of andere query en dan ben ik weer terug bij het begin...

[pedorus]

Je kan bijvoorbeeld beginnen door een view te maken voor Recipe Management die een union all van 100 statements bevat... Je kan hier evt Excel voor gebruiken. Voortaan gebruik je alleen die view. De kans is trouwens groot dat de database nu dubbelen bevat en er dus grondstoffen dubbel voorkomen.

[Verwijderd]

100 x herhalen hoeft niet persee...

Je kunt een string maken met een sql-statement die je vult in een while loop

Aan het eind execute je de sql string

Iets van:

SET @counter = 1
SET @mySql = 'SELECT '
WHILE @counter < 10
BEGIN
SET @mySql =@mySQL + 'blabla' + Convert(varchar(2), @counter)
SET @counter = @counter + 1
END
exec(@mySQL)