[sql] kan dit met sql (van w2k)

[shades]

Is het mogelijk om met transact-sql het volgende te doen..

Ik heb 3 tabellen

code:

|tblKledingstukken| |TblMaten:        | |tblKledingMaten: |
|-----------------| |-----------------| |-----------------|
|                 | |                 | |                 |
|ArtID: Artikel:  | |MaatID: Maten:   | |ArtIDx: MaatIDx: |
|------ --------  | |------- ------   | |------  -------  |
|11     Jas       | |21      XS       | |11      21       |
|12     Broek     | |22      S        | |11      23       |
|13     T-Shirt   | |23      M        | |11      24       |
|14     Overhemd  | |24      L        | |12      22       |
                    |25      XL       | |13      25       |
                    |26      XXL      | |13      26       |
                                        |14      23       |
                                        |14      24       |
                                        |14      25       |
                                        |14      26       |

Kan ik hier 1 tabel uitkrijgen die er als volgt uitziet:

code:

|Voorraad:                                |
|-----------------------------------------|
|Artikel:   XS:  S:   M:   L:   XL:  XXL: |
|--------   ---- ---- ---- ---- ---- ---- |
|Jas        1    0    1    1    0    0    |
|Broek      0    1    0    0    0    0    |
|T-Shirt    0    0    0    0    1    1    |
|Overhemd   0    0    1    1    1    1    |

Op deze manier kan ik namelijk heel snel een tabel in asp programmeren met vinkjes zodat de verkoper/ster direct kan zien of een opgegeven artikel nog in een bepaalde maat verkrijgbaar is..

Ik kan me voorstellen dat dit een moeilijke is maar ik kan me weer niet voorstellen dat het niet mogelijk zou zijn...

Ik weet ook niet precies hoe je dit noemt. Via joinen maak je van de inhoud van een kolom geen nieuwe kolomen aan... Heb dus niet gezocht omdat ik niet weet waarnaar. sorry

[Bud_s]

ziet er naar uit dat je een crosstab (kruistabel) moet maken.

[RobIII]

Of met cursors gaan werken, en dat wil je niet...

[shades]

Bud_s schreef op 14 April 2003 @ 22:51:
ziet er naar uit dat je een crosstab (kruistabel) moet maken.

Is dat cross join? at heb wel ergens voorbij zien komen...

RobIII schreef op 14 april 2003 @ 22:52:
Of met cursors gaan werken, en dat wil je niet...

[ Voor 27% gewijzigd door shades op 14-04-2003 22:54 ]

[RobIII]

shades schreef op 14 April 2003 @ 22:53:
[...]


Is dat cross join? at heb wel ergens voorbij zien komen...


[...]

<zucht> Heb je al eens in de help gekeken bij cursors?
Je kunt een soort "loopje" bouwen en daarmee dus een "temp"-tabelletje vullen zoals je dat zelf wilt. Niet erg efficient, niet erg mooi, maar wel één van de weinige oplossingen als je het "Sql-side" wilt doen. Anders moet je in je PHP, ASP of ADO of whatever code zelf gaan prutsen. En dan is SQL nog altijd sneller IMO.

[shades]

RobIII schreef op 14 April 2003 @ 23:02:
[...]

<zucht> Heb je al eens in de help gekeken bij cursors?
Je kunt een soort "loopje" bouwen en daarmee dus een "temp"-tabelletje vullen zoals je dat zelf wilt. Niet erg efficient, niet erg mooi, maar wel één van de weinige oplossingen als je het "Sql-side" wilt doen. Anders moet je in je PHP, ASP of ADO of whatever code zelf gaan prutsen. En dan is SQL nog altijd sneller IMO.

Ik heb zojuist (na je zucht) gekeken in de sqlhelp maar er zijn er zoveel items in de index alleen al dat ik niet precies weet welke ik nodig heb. De oplossing heb ik al gemaakt in asp maar dat is dus niet efficient.

[kenneth]

MSSQL: Je kan met OLAP cubes maken, en daarin kan het wel.
Access: Je kan een TRANSFORM-query maken (Kruistabelquery).

[marty]

hmmm...
Kun je niet gewoon een tabel maken met als kolommen
XS S M L XL XXL
?

Volgens mij kun je het dan met een GROUP BY op Artikel en een LEFT JOIN (waarbij je dus wel een waarde krijgt of NULL) ook oplossen

[Janoz]

Als je gebruik maakt van asp oid om de uiteindelijke pagina op te bouwen is het misschien handiger om met SQL de volgende tabel terug te krijgen:

kleding | maat | aantal

Dit is redelijk simpel met een count/sum + outer join te doen. Vervolgens kun je met ASP dit simpel omvormen in de gewenste tabel waneer je er ook voor zorgt dat de resultaten op maat en product worden gesorteerd.

[shades]

marty schreef op 14 april 2003 @ 23:16:
hmmm...
Kun je niet gewoon een tabel maken met als kolommen
XS S M L XL XXL
?

Volgens mij kun je het dan met een GROUP BY op Artikel en een LEFT JOIN (waarbij je dus wel een waarde krijgt of NULL) ook oplossen

Dat kan wel maar bij de maten tabel komt ook nog een beschrijving van de maat (bv xxl = extra extra large, beetje fout voorbeeld misschien maar daarom in die maten tabel gescheiden en zo kan de maatid ook aan een andere maatindeling gehaden worden (34/36/38 enzo)

[Janoz]

marty schreef op 14 April 2003 @ 23:16:
hmmm...
Kun je niet gewoon een tabel maken met als kolommen
XS S M L XL XXL
?

Volgens mij kun je het dan met een GROUP BY op Artikel en een LEFT JOIN (waarbij je dus wel een waarde krijgt of NULL) ook oplossen

Ja, en toen kwam er een extra groot/klein product en moet je je tabeldefinities wijzigen..

[MBV]

je zou een view kunnen maken.

code:

CREATE VIEW maten(kolom1,kolom2)
AS SELECT Kolom1,Kolom2
FROM Kledingstukken,Maten,Kledingmaten
WHERE Kledingstukken.ArtID=Kledingmaten.ArtIDx AND 
            Maten.MaatID=Kledingmaten.MaatIDx

Kolommen had ik geen zin om in te vullen, maar hiermee moet je een eind kunnen komen, lijkt me. Ik geloof dat dit is wat je bedoelt, of vergis ik me?

[ Voor 10% gewijzigd door MBV op 14-04-2003 23:23 . Reden: layout+toevoeging ]

[EfBe]

shades schreef op 14 april 2003 @ 22:49:
Is het mogelijk om met transact-sql het volgende te doen..

Ik heb 3 tabellen

code:

|tblKledingstukken| |TblMaten:        | |tblKledingMaten: |
|-----------------| |-----------------| |-----------------|
|                 | |                 | |                 |
|ArtID: Artikel:  | |MaatID: Maten:   | |ArtIDx: MaatIDx: |
|------ --------  | |------- ------   | |------  -------  |
|11     Jas       | |21      XS       | |11      21       |
|12     Broek     | |22      S        | |11      23       |
|13     T-Shirt   | |23      M        | |11      24       |
|14     Overhemd  | |24      L        | |12      22       |
                    |25      XL       | |13      25       |
                    |26      XXL      | |13      26       |
                                        |14      23       |
                                        |14      24       |
                                        |14      25       |
                                        |14      26       |

Kan ik hier 1 tabel uitkrijgen die er als volgt uitziet:

code:

|Voorraad:                                |
|-----------------------------------------|
|Artikel:   XS:  S:   M:   L:   XL:  XXL: |
|--------   ---- ---- ---- ---- ---- ---- |
|Jas        1    0    1    1    0    0    |
|Broek      0    1    0    0    0    0    |
|T-Shirt    0    0    0    0    1    1    |
|Overhemd   0    0    1    1    1    1    |

Ja hoor.

Ik heb een poging gedaan, je zult andere columns verder moeten invullen maar die zijn triviaal.

SQL:

SELECT  DISTINCT
    tblKledingstukken.Artikel,
    (
        SELECT COUNT(*) FROM tblMaten
        WHERE MaatID = tblKledingMaten.MaatIDx
        AND Maten='XS'
    ) AS XS,
    (
        SELECT COUNT(*) FROM tblMaten
        WHERE MaatID = tblKledingMaten.MaatIDx
        AND Maten='S'
    ) AS S,
    (
        SELECT COUNT(*) FROM tblMaten
        WHERE MaatID = tblKledingMaten.MaatIDx
        AND Maten='M'
    ) AS M
    -- ETC. Hier meer regels columndefinities plaatsen. 
    -- let erop dat je dan een ',' achter de M plaatst
    -- hierboven ;)
FROM    tblKledingstukken INNER JOIN tblKledingMaten
    ON 
    tblKledingstukken.ArtID = tblKledingMaten.ArtIDx

[EfBe]

RobIII schreef op 14 april 2003 @ 23:02:
<zucht> Heb je al eens in de help gekeken bij cursors?
Je kunt een soort "loopje" bouwen en daarmee dus een "temp"-tabelletje vullen zoals je dat zelf wilt. Niet erg efficient, niet erg mooi, maar wel één van de weinige oplossingen als je het "Sql-side" wilt doen. Anders moet je in je PHP, ASP of ADO of whatever code zelf gaan prutsen. En dan is SQL nog altijd sneller IMO.

No offence hoor, maar zuchten om iemand anders zn opmerking en dan zelf met de opmerking 'cursors' komen.

Misschien moet JIJ eens een boek over SQL lezen
(hint: cursors en 'loopjes' zijn not-done)

[RobIII]

EfBe schreef op 15 April 2003 @ 09:21:
[...]

No offence hoor, maar zuchten om iemand anders zn opmerking en dan zelf met de opmerking 'cursors' komen.

Misschien moet JIJ eens een boek over SQL lezen
(hint: cursors en 'loopjes' zijn not-done)

... Dat zeg ik...

RobIII schreef op 14 april 2003 @ 22:52:
Of met cursors gaan werken, en dat wil je niet...

En de oplossing hierboven (van jou) mag je elke keer aanpassen als er een nieuw veld nodig is. Dat is mooi wil je zeggen?

En dat je cursors moet vermijden weet ik verrekkes goed, ik ben altijd de eerste die dat roept:
Cursors & Performance
Cursors & Performance deel 2

...en ook ik ga voor Janoz' oplossing, net als whoami. Alleen als 'ie het SQL-side wil oplossen zal 'ie toch iets dergelijks moeten gebruiken, wil 'ie flexibibel blijven

[ Voor 40% gewijzigd door RobIII op 15-04-2003 10:03 ]

[whoami]

Ik zou voor de oplossing van Janoz gaan. Gebruik SQL waarvoor het dient: in dit geval het ophalen van de gegevens. SQL is niet gemaakt om de presentatie te verzorgen.

[EfBe]

RobIII schreef op 15 April 2003 @ 09:36:
En de oplossing hierboven (van jou) mag je elke keer aanpassen als er een nieuw veld nodig is. Dat is mooi wil je zeggen?

Dat zeg ik niet. Hij wilde zo'n tabel en die wordt door die query geleverd. De optimizer maakt er echt een mooie query van, dus ik zie het probleem niet echt. Uiteraard kan hij met wat joins en 2 nested loops in asp een gui bouwen ermee, eerlijk gezegd zou dat mijn voorkeur ook zijn, maar misschien wil hij dat niet.

En dat je cursors moet vermijden weet ik verrekkes goed, ik ben altijd de eerste die dat roept:
Cursors & Performance
Cursors & Performance deel 2

Cursors hoeven niet altijd slecht te zijn. Soms moet je wel. En een cursor aanmaken is gelijk aan het creeeren van een temptable, iets dat ook vaak erg handig kan zijn (alhoewel in Sqlserver 2000 je beter een 'table' variable kunt maken)

...en ook ik ga voor Janoz' oplossing, net als whoami. Alleen als 'ie het SQL-side wil oplossen zal 'ie toch iets dergelijks moeten gebruiken, wil 'ie flexibibel blijven

Lijkt op zich wel zo, maar dit kan HTML technisch erg irritant zijn, je moet nl. dan allerlei dingen gaan bijhouden wanneer je bv table rows hebt afgesloten en wanneer je een nieuwe moet beginnen (ik noem maar iets). Maar is wel veel flexibeler natuurlijk, het hangt er vanaf in hoeverre dat lijstje met matendefinities wijzigt. Die maten lijken me wel erg standaard. En om nou 5 uur extra werk te gaan creeeren voor een situatie die zeer waarschijnlijk nooit zal voorkomen... je kunt dan IMHO beter de afweging maken om indien dat uitzonderlijke geval zich voordoet, de query en de layout iets aan te passen. Niet alles hoeft tot in den treure flexibel te zijn, in tegendeel: veel programmeurs drammen tot in den treure door dat alles maar zo flexibel mogelijk moet worden opgesteld, terwijl dat in sommige gevallen onzinnige vormen aanneemt (dus dat je flexibel wilt zijn voor situaties die niet tot nauwelijks kunnen voorkomen).

Klinkt als een rijtje "Famous last words", maar het is een gegeven dat je beginnende softwaredevelopers herkent aan het overijverig flexibel maken van weet ik wat voor code, terwijl je op een gegeven moment een punt moet kiezen waarop je zegt: "ok, alle situaties die nu nog voorkomen en dus niet kunnen worden behandeld door deze code, worden individueel bekeken wanneer ze zich voordoen". Ik ben een tegenstander van eXtreme Programming, maar men mag best dieper nadenken over OF een flexibele benadering met meer overhead, meer code, meer ellende higher up de n-tierstack wel opweegt tegen de nadelen van het inflexibel zijn in sommige gevallen.

[ Voor 30% gewijzigd door EfBe op 15-04-2003 11:49 ]

[RobIII]

Ik kan me gedeeltelijk vinden in je post, ware het niet dat ik wel iemand ben die het liefst zo flexibel mogelijk is. Ten eerste wil ik effe opmerken dat ik geen beginner ben, maar al zo'n 7 jaar werkervaring heb en een dikke 15+ jaar programmeer. En mijn functie (en loon ) is daar ook naar dan. Maar daar wil ik verder niet op in gaan. Wel wil ik dus effe laten merken dat ik geen "beginner" ben.

EfBe schreef op 15 April 2003 @ 11:42:
Cursors hoeven niet altijd slecht te zijn. Soms moet je wel. En een cursor aanmaken is gelijk aan het creeeren van een temptable, iets dat ook vaak erg handig kan zijn (alhoewel in Sqlserver 2000 je beter een 'table' variable kunt maken)

Mee eens dus...

Waar ik voor streef is een goede opzet in den beginne. Als je je DB model goed voor mekaar hebt dan heb je de helft van het werk al gehad. Als je vervolgens daar al wat flexibiliteit in aanbrengt profiteer je daar in de "hogere" tiers ook van. Natuurlijk, en dat ben ik dus wel met je eens, moet je dit niet té ver door drijven.

Wat je zegt dat het moeilijk in HTML te maken is is ook niet waar. Er zijn zat constructies te bedenken die dat mooi oplossen. Maar dat laat ik aan de TS zelf over

[ Voor 26% gewijzigd door RobIII op 15-04-2003 12:20 ]

[Swa-baldie]

In oracle zou je een view maken van deze drie tabellen

[whoami]

Swa-baldie schreef op 15 april 2003 @ 12:09:
In oracle zou je een view maken van deze drie tabellen

AFAIK lost een view dat probleem niet op.

[EfBe]

RobIII schreef op 15 April 2003 @ 12:05:
Ik kan me gedeeltelijk vinden in je post, ware het niet dat ik wel iemand ben die het liefst zo flexibel mogelijk is. Ten eerste wil ik effe opmerken dat ik geen beginner ben, maar al zo'n 7 jaar werkervaring heb en een dikke 15+ jaar programmeer. En mijn functie (en loon ) is daar ook naar dan. Maar daar wil ik verder niet op in gaan. Wel wil ik dus effe laten merken dat ik geen "beginner" ben.

*pfew*! Niet overklast qua ervaring

[...]
Mee eens dus...
Waar ik voor streef is een goede opzet in den beginne. Als je je DB model goed voor mekaar hebt dan heb je de helft van het werk al gehad. Als je vervolgens daar al wat flexibiliteit in aanbrengt profiteer je daar in de "hogere" tiers ook van. Natuurlijk, en dat ben ik dus wel met je eens, moet je dit niet té ver door drijven.

Precies. Een setje maten is, hier dus het geval, volgens mij zo star dat je dat nauwelijks hoeft te wijzigen in die zin dat je een extra kolom erbij krijgt.

Wat je zegt dat het moeilijk in HTML te maken is is ook niet waar. Er zijn zat constructies te bedenken die dat mooi oplossen. Maar dat laat ik aan de TS zelf over

Beetje lulkoek, want als je een resultset krijgt met een n:m relatie erin geprakt en jij moet die in een 2D tabel middels html tonen, krijg je toch ranzige code zoals "Hey, een nieuwe rij nodig, oude rij afsluiten, nieuwe aanmaken.. " en dan na het loopenstelsel nog eens de laatste rij afsluiten (been there done that). ASP.NET om een voorbeeld te noemen lost dit ge-emmer op, asp echter is een ander verhaal (php neem ik aan idem)

[shades]

Sorry dat ik gisteravond ineens weg was (zzz) maar mij ogenleden zakte naar mijn voeten.
Ik heb al jullie replies even doorgenomen. Twee interessante links over cursors die ik als ik wat frisser ben even ga lezen.

Ik weet dat got niet bedoelt is om kant-en-klare antwoorden van te verwachten maar de oplossing die EfBe bedacht heeft is eigenlijk precies wat ik zocht.


Ik heb het voorbeeld wat je (EfBe) gaf uitgevoerd en krijg dit als resultaat:
(waardes komen niet helemaal overeen met het voorbeeld van boven)

code:

Artikel    XS          S           M           
---------- ----------- ----------- ----------- 
Broek      0           0           0
Broek      0           1           0
Broek      1           0           0
Jas        0           0           0
Jas        0           1           0
Jas        1           0           0
Overhemd   0           0           0
T-Shirt    0           0           0
T-Shirt    0           1           0

(9 row(s) affected)

De distinct lijkt niet helemaal goed te werken.

[ Voor 4% gewijzigd door shades op 15-04-2003 18:04 ]

[EfBe]

Hmm inderdaad, het was ook "Code uit het hoofd opgeschreven", dus dat is soms niet correct Ik had idd die andere columns over het hoofd gezien.

je kunt dit eventueel ondervangen door eerst een distinct select te doen op tblKledingstukken en die te inserten in een temptable (#Kledingstukken oid) middels een insert into select combi, en daar dan een join mee doen maar dit wordt dan wel wat traag. Ik denk dat je toch het moet zoeken in een routine in de gui.

edit: een group by toevoegen helpt denk ik. (groupen over alle columns in de select list.

[ Voor 11% gewijzigd door EfBe op 15-04-2003 18:15 ]

[shades]

EfBe schreef op 15 April 2003 @ 18:14:
Hmm inderdaad, het was ook "Code uit het hoofd opgeschreven", dus dat is soms niet correct Ik had idd die andere columns over het hoofd gezien.

je kunt dit eventueel ondervangen door eerst een distinct select te doen op tblKledingstukken en die te inserten in een temptable (#Kledingstukken oid) middels een insert into select combi, en daar dan een join mee doen maar dit wordt dan wel wat traag. Ik denk dat je toch het moet zoeken in een routine in de gui.

edit: een group by toevoegen helpt denk ik. (groupen over alle columns in de select list.

Ga ik na het eten even proberen.. Zal wel een hoop gezoek worden in de books online maar dat geeft niet...


PS..
Die gui-routine had ik en heb het alsvolgt opgelost :
Open recordset van alle maten (maten en maatid) en plaats die in een array via GetRows. Recordset sluiten. Dan weet ik dus hoeveel kolommen er moeten komen in de htmltabel.
Vervolgens open in dan de Kledingstukken + ArtID en door loop deze.
Per ArtID loop ik dan door de GetRows array heen die de matenid's afloopt. Dan heb ik dus een lijstje van artid(x) en de maatid(x). Deze stop ik dan in een fuctie in kijk of het aanwezig is.

Grote nadeel is dat bij die functie dus iedere keer een connectie geopend en gesloten wordt. Valt nog te optimalizeren natuurlijk.. Daarom vroeg ik ook naar een sql oplossing..

[ Voor 32% gewijzigd door shades op 15-04-2003 18:37 ]

[EfBe]

Je kunt het ook in 1 query doen, zoals janoz heeft opgegeven en dan daarnaast 1 of meerdere columns toevoegen in de resultset die een count doen op verschillende waarden, zodat je in de resultset weet hoeveel rijen je moet maken. Die heb je dan wel in elke rij zitten, maar dat boeit niet, je kunt ze meteen uitlezen en je code er meteen op aanpassen. Je hoeft dan dus 1 recordset te openen en 1 keer de recordset door te lopen voor alle rijen en kolommen in je gui.

[shades]

EfBe schreef op 15 april 2003 @ 18:50:
Je kunt het ook in 1 query doen, zoals janoz heeft opgegeven en dan daarnaast 1 of meerdere columns toevoegen in de resultset die een count doen op verschillende waarden, zodat je in de resultset weet hoeveel rijen je moet maken. Die heb je dan wel in elke rij zitten, maar dat boeit niet, je kunt ze meteen uitlezen en je code er meteen op aanpassen. Je hoeft dan dus 1 recordset te openen en 1 keer de recordset door te lopen voor alle rijen en kolommen in je gui.

Maar Janoz heeft het over een aantallen kolom en daar ben ik dus niet in geinteresseert. De tabel die ik wil hebben geeft aan of iets leverbaar is in die maat of niet ja/nee, 1/0 etc. meer info staat er in die tabellen ook niet.

[shades]

Ik ben er geloof ik uit:

SQL:

drop table #testtable
create table #testtable
    (
        artid int,
        artikel varchar(10),
        xs tinyint,
        s tinyint,
        m tinyint
    )
insert into #testtable
    SELECT DISTINCT
        tblKledingstukken.Artid,tblKledingstukken.Artikel,
            (
                SELECT COUNT(*) FROM tblMaten
                WHERE MaatID = tblKledingMaten.MaatIDx
                AND Maten='XS'
            ) AS XS,
        
            (
                SELECT COUNT(*) FROM tblMaten
                WHERE MaatID = tblKledingMaten.MaatIDx
                AND Maten='S'
            ) AS S,

            (
                SELECT COUNT(*) FROM tblMaten
                WHERE MaatID = tblKledingMaten.MaatIDx
                AND Maten='M'
            ) AS M
            -- ETC. Hier meer regels columndefinities plaatsen. 
            -- let erop dat je dan een ',' achter de M plaatst
            -- hierboven ;)
FROM    tblKledingstukken INNER JOIN tblKledingMaten
    ON 
    tblKledingstukken.ArtID = tblKledingMaten.ArtIDx

select artikel, sum(xs) as 'XS' , sum(s) as 'S' , sum(m) as 'M'
from #testtable
GROUP BY artikel WITH rollup

Produceert dit:

code:

artikel    XS          S           M           
---------- ----------- ----------- ----------- 
Broek      1           1           0
Jas        1           1           0
Overhemd   0           0           0
T-Shirt    0           1           0
NULL       2           3           0

(5 row(s) affected)

Alleen als ik in de laatste select artid erbij doe dan gaat ie over zijn nek.. Ik snap wel waarom maar niet hoe ik het op kan lossen...
Die null komt van de rollup. Irri maar valt (heel goed) mee te leven


Is dit een beetje een goede approach ?

[ Voor 39% gewijzigd door shades op 15-04-2003 21:37 ]

[EfBe]

erm, nee. (die rollup hoeft niet btw, dat is voor cubes)

Ik heb deze query verzonnen hieronder. In een andere thread vroeg ik om een toepassing van CROSS JOIN, want ik kon er geen bedenken. Welnu, jouw probleem IS een toepassing van de cross join, dus ik dacht laat ik de query even bouwen. hij is als volgt:

SQL:

SELECT  tblKledingstukken.ArtID,
    tblKledingstukken.Artikel,
    tblMaten.Maten,
    ISNULL(tblKledingmaten.MaatIDx, 0) AS HasMaat
FROM    (tblKledingstukken CROSS JOIN tblMaten)
    LEFT JOIN tblKledingMaten
    ON 
    tblMaten.MaatID = tblKledingMaten.MaatIDx
ORDER BY tblKledingstukken.ArtID ASC,
    tblMaten.MaatID ASC

Het idee is als volgt: voor elk artikel dat je in je database hebt, join je alle maten die je kent (in tblMaten) er aan vast. Dit is de cross join. Omdat dit combinaties oplevert die je eventueel niet hebt in de tabel tblKledingMaten, kun je mooi een left toepassen om dat naar boven te krijgen, vandaar de left join met die tabel.

Je krijgt dan in de kolom HasMaat (bedenk zelf een andere naam ) af en toe een 0, af en toe een getal > 0. Een getal groter dan 0 is de id van de maat die leverbaar is voor dat kledingstuk. Heb je een 0 dan is het kledingstuk (het artikel) niet leverbaar in die maat.

Omdat je ALTIJD evenveel rows hebt per artikel (want je crossjoint met tblMaten), kun je de recordset die je krijgt van boven naar beneden aflopen, en wanneer je een nieuw artikel ziet (de ArtID van de huidige row is verschillend van de vorige row in de resultset) dan maak je een nieuwe table row aan in je html. Per rij in die table in je html schrijf je in de 1e kolom de waarde van Artikel, in de andere kolommen per record een "Ja" wanneer HasMaat > 0 of "Nee" als HasMaat == 0.

Wil je het aantal rows weten per artikel, plak dan een extra column in de resultset met de count(*) op tblMaten. Je moet dan daar 1 bij optellen voor je kolom voor 'artikel'.

Succes

[shades]

Ik weet niet precies wat je wilt maar het resultaat is een beetje niet helemaal goed. Hier een stukje:

code:

ArtID       Artikel    Maten      HasMaat     
----------- ---------- ---------- ----------- 
1           Jas        XS         1
1           Jas        XS         1
1           Jas        S          2
1           Jas        S          2
1           Jas        S          2
1           Jas        M          0
1           Jas        L          4
1           Jas        L          4
1           Jas        XL         5
1           Jas        XL         5
1           Jas        XXL        6
1           Jas        XXL        6
2           Overhemd   XS         1
2           Overhemd   XS         1
2           Overhemd   S          2
2           Overhemd   S          2
2           Overhemd   S          2
2           Overhemd   M          0
2           Overhemd   L          4
2           Overhemd   L          4
2           Overhemd   XL         5
2           Overhemd   XL         5
2           Overhemd   XXL        6
2           Overhemd   XXL        6

Ik ben zelf meer gecharmeerd van je eerste oplossing.. Deze heb ik in een #temptable gestopt waarop ik de volgende query loslaat (had ik al gegeven maar een beetje aangepast.:

SQL:

select artid, artikel, sum(xs) as 'XS' , sum(s) as 'S' , sum(m) as 'M'
from #testtable
GROUP BY artid, artikel

en dat wordt dan

code:

artid       artikel    XS          S           M           
----------- ---------- ----------- ----------- ----------- 
3           Broek      1           1           0
1           Jas        1           1           0
2           Overhemd   0           0           0
4           T-Shirt    0           1           0

Ik probeer nu te kijken of ik het stuk wat jij gemaakt hebt zo te maken dat alle maatkolommen automatisch toegevoegd worden aan die select.. Dat probeer ik zelf uit te zoeken

[Brothar]

Ehm,
Je wilt dat de kassière kan zien wat nog op voorraad is. Dan heeft ze al 1 bepaald artikel gekozen. Dan kun je dus een selectie draaien, op de view, voor dat ene artikel, gesorteerd op maat.
Ik denk aan zoiets als:
select *
from kledingstukken ks, kledingmaten km, maten m
where (ks.id=km.kledingstukkenid and m.id=km.matenid) and
( ks.artikelid= variabele_artikel)
order by maten

Waarbij variable_artikel het id vanhet eerder geselecteerd artikel is, dat in een geheugenvariabele is opgeslagen
(kan tenminste in delphi)

Je krijgt dan van dat ene artikel onder elkaar te zien welke maat wel/niet op voorraad is.
(andere artikelen is ze toch niet in geïnteresseerd ?)

De hier boven geposte oplossing met "Create Table" is een mooie voor een totaaloverzicht voor de bedrijfsmanager die zich bezig houdt met voorraadbeheer.

[ Voor 24% gewijzigd door Brothar op 16-04-2003 21:59 ]

[shades]

Brothar schreef op 16 April 2003 @ 21:47:
Ehm,
Je wilt dat de kassière kan zien wat nog op voorraad is. Dan heeft ze al 1 bepaald artikel gekozen

....

Nee daar gaat het niet om. Niet geinteresseerd in de voorraad. De tabel moet een overzicht geven of iets uberhaupt bestaat/geleverd kan worden. Meer info is er ook niet.
Zie het als een lijst met features van een stereoinstallatie. Verkrijgbaar in de kleuren wit en zwart, maar niet in goud, wel stereo, geen dts.. niets meer en niets minder.


Voor robIII en de mensen die cursors afraden. Ik heb die twee pagina's gelezen en ben ook in books online gaan kijken wat je kan met cursors. Aan de hand van die hulp heb ik het volgende scriptje gemaakt die dus dymanisch de subqueries van EfBe maakt:
Ik gebruik gemaakt van een print zodat je direct ziet wat hij doet. Deze meuk moet ik een @variable komen en dan exec sp_executesql @variable.
(ik had de tabjes willen verkleinen in de code= maar kon zou snel niet vinden hoe dat moest)

SQL:

-- aangepast voorbeeld van sql2000 help

use     cms_ontw
declare maten_cursor cursor
for     select Maten from tblMaten

open maten_cursor
    declare @maat nvarchar(10)
    
    fetch next from maten_cursor into @maat
    while (@@fetch_status <> -1) begin
       if (@@fetch_status <> -2) begin
          select @maat = ltrim(rtrim(@maat))
            -- maak subquery
            print '(' + char(13)
            print ' SELECT COUNT(*) FROM tblMaten' + char(13)
            print   ' WHERE MaatID = tblKledingMaten.MaatIDx' + char(13)
            print ' AND Maten=''' + @maat + '''' + char(13)
            print ') AS ' + @maat + char(13)
            print char(13)
            --print @maat
       end --if
       fetch next from maten_cursor into @maat
    end --while
close maten_cursor
deallocate maten_cursor

[ Voor 21% gewijzigd door shades op 16-04-2003 23:21 ]

[EfBe]

shades schreef op 16 April 2003 @ 21:36:
Ik weet niet precies wat je wilt maar het resultaat is een beetje niet helemaal goed. Hier een stukje:

code:

ArtID       Artikel    Maten      HasMaat     
----------- ---------- ---------- ----------- 
1           Jas        XS         1
1           Jas        XS         1
1           Jas        S          2
1           Jas        S          2
1           Jas        S          2
1           Jas        M          0
1           Jas        L          4
1           Jas        L          4
1           Jas        XL         5
1           Jas        XL         5
1           Jas        XXL        6
1           Jas        XXL        6
2           Overhemd   XS         1
2           Overhemd   XS         1
2           Overhemd   S          2
2           Overhemd   S          2
2           Overhemd   S          2
2           Overhemd   M          0
2           Overhemd   L          4
2           Overhemd   L          4
2           Overhemd   XL         5
2           Overhemd   XL         5
2           Overhemd   XXL        6
2           Overhemd   XXL        6

Ik ben zelf meer gecharmeerd van je eerste oplossing.. Deze heb ik in een #temptable gestopt waarop ik de volgende query loslaat (had ik al gegeven maar een beetje aangepast.:

Hmm. Ik snap de dubbele rijen niet echt, waar die vandaan komen. Maar die zijn iig met een distinct eruit te filteren. Het lijstje is dan wel goed bruikbaar hoor. En erg veel sneller dan je temptable oplossing, die bij een paar duizend rows toch wel trager begint te worden.

[Verwijderd]

En wat vind je van mijn oplossing :-)

Dit is DE manier om een crosstabel-query na te bootsen.
Helaas moet je van te voren wel weten welke kolommen er bestaan. Maar omdat je dat al weet (de waarden staan immers in tblMaten) zou je deze query dynamisch kunnen generen.

SELECT tblKledingstukken.Artikel,
Min(IIf([TblMaten]![Maten]="XS",1)) AS XS,
Min(IIf([TblMaten]![Maten]="S",1)) AS S,
Min(IIf([TblMaten]![Maten]="M",1)) AS M,
Min(IIf([TblMaten]![Maten]="L",1)) AS L,
Min(IIf([TblMaten]![Maten]="XL",1)) AS XL,
Min(IIf([TblMaten]![Maten]="XXL",1)) AS XXL
FROM (tblKledingMaten INNER JOIN tblKledingstukken ON tblKledingMaten.ArtIDx = tblKledingstukken.ArtID)
INNER JOIN TblMaten ON tblKledingMaten.MaatIDx = TblMaten.MaatID
GROUP BY tblKledingstukken.Artikel, tblKledingstukken.ArtID
ORDER BY tblKledingstukken.ArtID;

ps. Deze query is gemaakt in access maar het kan ook in MsSQL of in Oracle met de decode functie.

[ Voor 16% gewijzigd door Verwijderd op 17-04-2003 10:58 ]

[Verwijderd]

En ??? heeft deze oplossing je op weg kunnen helpen ??

[shades]

Verwijderd schreef op 17 april 2003 @ 12:19:
En ??? heeft deze oplossing je op weg kunnen helpen ??

euh, ben net thuis ik werk gewoon
Ga het zeker even proberen.

edit:
mssql valt over de '!' : Min(IIf([TblMaten]![Maten]="XS",1)) AS XS,

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '!'.

Min(IIf(tblMaten.Maten="XS",1)) AS XS gaat beter, dan wordt AS ook begrepen maar blijft dan hangen op de '='


Als hij werkt is ie wel mooi trouwens

[ Voor 63% gewijzigd door shades op 17-04-2003 21:21 ]

[Verwijderd]

Het werkt ook echt!! Ik heb jouw tabellen in Access aangemaakt en ook jouw data ingevoerd. En deze query geeft dan precies de resultaat die jij wilt hebben.

Helaas ken ik de syntax van mssql niet meer. Maar even stoeien en dan kom je er wel uit :-)

[EfBe]

Die '!' moet je vervangen door '.' dus [tblMaten]![Maten] wordt tblMaten.Maten

[shades]

EfBe schreef op 18 April 2003 @ 11:04:
Die '!' moet je vervangen door '.' dus [tblMaten]![Maten] wordt tblMaten.Maten

euh hallo
Dat heb ik gedaan. kijk maar in de reply boven je...

heb nu dus dit:

SQL:

SELECT tblKledingstukken.Artikel, 
Min(IIf(tblMaten.Maten = 'XS',1)) AS XS,
Min(IIf(tblMaten.Maten = 's',1)) AS S,
Min(IIf(tblMaten.Maten = 'M',1)) AS M,
Min(IIf(tblMaten.Maten = 'L',1)) AS L,
Min(IIf(tblMaten.Maten = 'XL',1)) AS XL,
Min(IIf(tblMaten.Maten = 'XXL',1)) AS XXL
FROM (tblKledingMaten INNER JOIN tblKledingstukken ON tblKledingMaten.ArtIDx = tblKledingstukken.ArtID)
INNER JOIN TblMaten ON tblKledingMaten.MaatIDx = TblMaten.MaatID
GROUP BY tblKledingstukken.Artikel, tblKledingstukken.ArtID
ORDER BY tblKledingstukken.ArtID;

Krijg dit terug:

code:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.

Ik heb naar het IIF statement gekeken en die snapt ie wel alleen denk ik niet dat het op de juist manier is toegepast...

[ Voor 71% gewijzigd door shades op 18-04-2003 11:23 ]

[EfBe]

IIF is niet bedoeld voor normale queries maar voor analysis services. Ik zit niet zo in de syntaxis van access, kan je dus ook geen SqlServer alternatief voorschotelen, anders dan een Case maar dat lijkt me onzin.

[shades]

EfBe schreef op 18 April 2003 @ 11:38:
IIF is niet bedoeld voor normale queries maar voor analysis services. Ik zit niet zo in de syntaxis van access, kan je dus ook geen SqlServer alternatief voorschotelen, anders dan een Case maar dat lijkt me onzin.

Dat IIF by analysis hoort (geen idee dat het bestond) kwam ik ook net achter.. Jammer wel.. want het is een hele compacte query. Raar dat het in access wel kan. Ik denk toch dat ik of met die sub queries ga werken of een efficiente gui-oplossing proberen..

[EfBe]

shades schreef op 18 april 2003 @ 11:43:
Dat IIF by analysis hoort (geen idee dat het bestond) kwam ik ook net achter.. Jammer wel.. want het is een hele compacte query. Raar dat het in access wel kan. Ik denk toch dat ik of met die sub queries ga werken of een efficiente gui-oplossing proberen..

Nou, ik begrijp wel wat IIF() doet (dat is een compacte if then, dus iif(expressie, thenclause) ), maar ik snap niet wat die functionaliteit in de min() functie doet, want dat is een aggregate over een column en die wordt niet opgegeven in de access-syntax Dus, ik snap eigenlijk geen hout van die access query

[Goodielover]

MrDreamOn geeft de oplossing.
Het is een zogenaamde MAX(DECODE()) oplossing.

De aggretatie is nodig omdat je van meerdere records naar 1 record gaat
Ik zou trouwens de MAX nemen en ook de else tak van de IF opnemen met een 0
Dan krijg je direct de nullen en enen.

Ik denk trouwens dat je wilt outer joinen om ook de artikelen waarvan de beschikbaarheid nul is naar voren te krijgen

[EfBe]

Goodielover schreef op 18 April 2003 @ 12:45:
MrDreamOn geeft de oplossing.
Het is een zogenaamde MAX(DECODE()) oplossing.

Wat houdt dit in? Sqlserver kent geen Decode().
Ik kijk nu ff in de Oracle help (zoals op:
http://www.csis.gvsu.edu/.../functions33a.htm#1017439 )
en ik ken 1 2 3 geen functie in sqlserver die dat hetzelfde doet. Je zult op SqlServer dus subqueries moeten maken, de query die ik dus in mn eerste posting
postte in deze thread.

(edit)
Gezien het voorbeeld op die page zou je denken aan een CASE clause in de select's resultset. Maar die is niet te embedden in een MAX aggregate volgens mij.

[ Voor 14% gewijzigd door EfBe op 18-04-2003 13:57 ]

[shades]

EfBe schreef op 18 April 2003 @ 13:56:
[...]

(edit)
Gezien het voorbeeld op die page zou je denken aan een CASE clause in de select's resultset. Maar die is niet te embedden in een MAX aggregate volgens mij.

Ik heb al geprobeert om de subqueries te 'summen' maar sql geeft dan "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." terug.

Kan dus niet..

[shades]

Misschien ben ik wel te ingewikkeld bezig. Ik heb nog eens gekeken naar de informatie in de kolomen. De tblKledingMaten tabel bevat alleen waarden die aanwezig zijn.
Als ik nu een kolom toevoeg die de naam "Besteldbaar" (als bit) en alle maatid aand maatidx toevoeg en dat bestelbaar 1 of 0 maak. Dan is het volgens mij nog steeds genormalizeerd en kan ik ook simpelere de tabel opbouwen en zonder de subqueries.

De tblKledingMaten tabel zal dan wel lang worden (kledingstukken * maten) maar dat maakt toch niet zoveel uit ?

[Verwijderd]

Hallo allemaal, wat hebben jullie toch weinig vertrouwen in mij en in MSSQL !!!

Om te bewijzen dat ik geen onzin loop te vertellen heb ik even MSSQL geinstalleerd. Daar heb ik gebruik gemaakt van de Books Online om te spieken op welke manier MSSQL, de DECODE functie van Oracle of de IIF functie van Access heeft geimplementeerd. Hier moet je dus gebruik maken van de CASE-WHEN-THEN-ELSE functie

Met deze informatie is het dus vrij simpel om de query een beetje aan te passen om het werkend te krijgen.

De oplossing moet dus zijn :

SELECT tblKledingstukken.Artikel,
max(case TblMaten.Maten when "XS" then 1 else 0 end) as XS,
max(case TblMaten.Maten when "S" then 1 else 0 end) as S,
max(case TblMaten.Maten when "M" then 1 else 0 end) as M,
max(case TblMaten.Maten when "L" then 1 else 0 end) as L,
max(case TblMaten.Maten when "XL" then 1 else 0 end) as XL,
max(case TblMaten.Maten when "XXL" then 1 else 0 end) as XXL
FROM tblKledingMaten INNER JOIN
tblKledingstukken ON
tblKledingMaten.ArtIDx = tblKledingstukken.ArtId INNER JOIN
TblMaten ON tblKledingMaten.MaatIDx = TblMaten.MaatID
GROUP BY tblKledingstukken.Artikel, tblKledingstukken.ArtId
ORDER BY tblKledingstukken.ArtId

ps. De query in deze form werkt dus alleen in mssql.

(Ik gebruik nu de max() functie inplaats van de min() want mssql vindt NULL minder dan 1 )
(De ORDER BY is eigelijk niet nodig maar als je het weg laat dan komt Jas niet boven Broek.)

[ Voor 30% gewijzigd door Verwijderd op 18-04-2003 23:12 ]

[Verwijderd]

EfBe schreef op 18 april 2003 @ 11:38:
IIF is niet bedoeld voor normale queries maar voor analysis services. Ik zit niet zo in de syntaxis van access, kan je dus ook geen SqlServer alternatief voorschotelen, anders dan een Case maar dat lijkt me onzin.

Nee hoor, dat is juist goed. Kijk maar naar mijn laatste post.

[Verwijderd]

EfBe schreef op 18 April 2003 @ 11:55:
[...]

Nou, ik begrijp wel wat IIF() doet (dat is een compacte if then, dus iif(expressie, thenclause) ), maar ik snap niet wat die functionaliteit in de min() functie doet, want dat is een aggregate over een column en die wordt niet opgegeven in de access-syntax Dus, ik snap eigenlijk geen hout van die access query

Ik weet niet precies hoe ik dit het beste uit kan leggen.
De min (of max) functie is nodig omdat je wil groeperen op Artikel. De waarden uit kolom Maten verdeel je over verschillende kolommen afhankelijk van de waarde.
Wanneer de query bij Jas de waarde XS tegenkomt wordt dat een 1 anders NULL. We zijn alleen geinteresseerd in de 1-nen en dat blijft ook zichtbaar door de min functie.

(Je moet het doorhebben om het te snappen )

[Verwijderd]

EfBe schreef op 18 April 2003 @ 13:56:
[...]

Wat houdt dit in? Sqlserver kent geen Decode().
Ik kijk nu ff in de Oracle help (zoals op:
http://www.csis.gvsu.edu/.../functions33a.htm#1017439 )
en ik ken 1 2 3 geen functie in sqlserver die dat hetzelfde doet. Je zult op SqlServer dus subqueries moeten maken, de query die ik dus in mn eerste posting
postte in deze thread.

(edit)
Gezien het voorbeeld op die page zou je denken aan een CASE clause in de select's resultset. Maar die is niet te embedden in een MAX aggregate volgens mij.

Dat heb je fout !!! Het is dus wel mogelijk. Het is hier dus onnodig en duur om subqueries te gebruiken.

[shades]

Wauw zeg...
Ik (en waarschijnlijk de rest ook wel) ben je heel dankbaar. Ik moest alleen een de " vervangen voor ' anders begreep de query analyzer het niet.. Ben gelijk op zoek gegaan naar de werking van de case en de voorbeeldjes bekeken en begrijp nu ook wat je bedoelt met het gebruik van MAX..
Ik ben goed geholpen.. thx all

[Goodielover]

Verwijderd schreef op 18 April 2003 @ 22:23:
Hallo allemaal, wat hebben jullie toch weinig vertrouwen in mij en in MSSQL !!!

Niet allemaal hoor. Jij haalde mij oplossingstechnisch de woorden uit de mond.

Je bent wel de eerste hier op GoT die ik ken die ook die oplossing kent.
Gelukkig zijn er dus meer SQL-deskundigen.

[Verwijderd]

Goodielover schreef op 18 April 2003 @ 23:33:
[...]

Niet allemaal hoor. Jij haalde mij oplossingstechnisch de woorden uit de mond.

Je bent wel de eerste hier op GoT die ik ken die ook die oplossing kent.
Gelukkig zijn er dus meer SQL-deskundigen.

Een tijdje terug had ik ook een cross-query nodig binnen Oracle. Maar tot mijn verbazing bestaat dat dus niet. Na een flinke zoek actie in Oracle-Groups (Deja) vond ik deze oplossing.

Ik ben er ondertussen achter gekomen dat bijna alles mogelijk is in SQL. Ik heb als het ware Het licht gezien.

ps. Bedank voor de compliment

[shades]

Ik wilde nog even kwijt dat de performance winst aanzienlijk is. Er staan op het moment 20 artikelen in en met mijn alles behalve efficiente gui-oplossing duurde het bijna 2 tellen voordat ik mij asp pagina kreeg. Nu komt de pagina zonder merkbare vertraging.. Gewoon geweldig !

[EfBe]

Verwijderd schreef op 18 april 2003 @ 23:06:
Dat heb je fout !!! Het is dus wel mogelijk. Het is hier dus onnodig en duur om subqueries te gebruiken.

Jaja Ik snap het al
Subqueries zijn overigens niet automatisch duur hoor. 10 tegen 1 dat het execution plan van jouw min aggregate oplossing niet veel verschilt van de subquery oplossing.

edit: ik kende CASE alleen van het bestuderen van de SELECT clauses, en verder sporadisch een paar keer gebruikt in de gevallen die dus verwoord zijn in BoL. Het is in BoL niet duidelijk dat je CASE dus idd ook binnen aggregate functie kunt gebruiken. Tijd dat MS dat eens update

[ Voor 27% gewijzigd door EfBe op 19-04-2003 19:02 ]

[EfBe]

Verwijderd schreef op 19 april 2003 @ 00:18:
Een tijdje terug had ik ook een cross-query nodig binnen Oracle. Maar tot mijn verbazing bestaat dat dus niet. Na een flinke zoek actie in Oracle-Groups (Deja) vond ik deze oplossing.

http://www.csis.gvsu.edu/...atements_103a.htm#2080373

quote:
"CROSS JOIN
The CROSS keyword indicates that a cross join is being performed. A cross join produces the cross-product of two relations and is essentially the same as the comma-delimited Oracle notation."

Zit er al jaaaaren in hoor

[ Voor 10% gewijzigd door EfBe op 19-04-2003 19:20 ]

[BierPul]

Is het niet makkelijker voor je om het Bitwise te doen

maatBits
1 S
2 M
4 L
8 XL
16 XXL

Dan kan je alles in 2 tabellen pleuren en klaar

Artikelen
artID maatBit
1 - 5 Geeft alleen op small en large true
2 - 1 Geeft alleen small terug



Voor je tabel check je dan voor elke maat gewoon of de bit gezet is

PHP:

// check voor L
if($maatBit & 4) { vink }

[ Voor 9% gewijzigd door BierPul op 19-04-2003 20:02 . Reden: typo ]

[Verwijderd]

EfBe schreef op 19 april 2003 @ 19:06:
[...]

http://www.csis.gvsu.edu/...atements_103a.htm#2080373

quote:
"CROSS JOIN
The CROSS keyword indicates that a cross join is being performed. A cross join produces the cross-product of two relations and is essentially the same as the comma-delimited Oracle notation."

Zit er al jaaaaren in hoor

Ik had het over de Crosstabelquery !!! niet over een cross join ..... (Je weet wel, de kruistabelquery van Access) en die bestaat toch echt niet in Oracle

[shades]

LOST_GUNT schreef op 19 april 2003 @ 19:58:
Is het niet makkelijker voor je om het Bitwise te doen

maatBits
1 S
2 M
4 L
8 XL
16 XXL

[..]

Is in een andere situatie misschien handig maar ik heb nu wat ik wil dankzij de oplossing van mrdreamon. Ik heb ik asp nu slechts twee lussen namelijk 1ste die de kolom-namen print en 1tje die de records print en klaar..

[EfBe]

Verwijderd schreef op 19 April 2003 @ 20:40:
[...]
Ik had het over de Crosstabelquery !!! niet over een cross join ..... (Je weet wel, de kruistabelquery van Access) en die bestaat toch echt niet in Oracle

oooooooooh een pivot-table bedoel je!

[shades]

EfBe schreef op 20 April 2003 @ 00:31:
[...]

oooooooooh een pivot-table bedoel je!

En daar kan ik dus wel wat over vinden in mssql-help.. nooit geweten dat dat zo genoemd werd..

[EfBe]

"draaitabel" is een andere benaming. In Excel wordt het veel gebruikt, om grafieken te maken gebaseerd op snijpunten van assen. Als je excel hebt, kun je daar ook meer info over vinden

[shades]

EfBe schreef op 20 April 2003 @ 12:15:
"draaitabel" is een andere benaming. In Excel wordt het veel gebruikt, om grafieken te maken gebaseerd op snijpunten van assen. Als je excel hebt, kun je daar ook meer info over vinden

Op mijn werk heb ik wel excel geinstalleerd maar nog nooit gestart