Kreuztabellen

[orangebarcode]

Hi,
Ich hab ein problem mit der korrekten Darstellung(also so wie ich es eigentlich haben möchte ^^) meiner Kreuztabelle...

Folgende Situation:

Ich besitze 2 tabellen in einer Datenbank um artikel zu verwalten:
Die erste Tabelle ist die Artikel Tabelle. Darin stehen die wichtigsten Information über jeden einzelnen Artikel. Jeder Artikel gehört einer Artikelgruppe an und besitzt eine eigene ArtikelID und ist unterteilt in Farben grössen.
D.h. ich arbeite in 3 Dimensionen: Gruppe, Grösse, Farbe.

159

Die zweite Tabelle ist die Auftragsposition Tabelle. Darin werden die Gruppe, die ID, Name, und zu einem Eintrag zusammengefasst die Farbe und Grösse (das muss auch so bleiben!) sowie die Menge erfasst.

160

Der code für die Artikeltabelle:
--
-- Tabellenstruktur für Tabelle `artikel`
--

CREATE TABLE `artikel` (
`id` int(11) NOT NULL auto_increment,
`artnr` varchar(255) NOT NULL default '',
`artid` varchar(255) NOT NULL default '',
`bez2` varchar(255) NOT NULL default '',
`groesse` varchar(255) NOT NULL default '',
`bez1` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Daten für Tabelle `artikel`
--

INSERT INTO `artikel` VALUES (1, 'E07664', 'bbb-aaa-111', 'noir', 'T1', 'Anouch');
INSERT INTO `artikel` VALUES (2, 'E07664', 'bbb-aaa-222', 'noir', 'T2', 'Anouch');
INSERT INTO `artikel` VALUES (3, 'E07221', 'aaa-bbb-111', 'blanc', '32', 'Manou');
INSERT INTO `artikel` VALUES (4, 'E07222', 'aaa-bbb-222', 'blanc', '34', 'Mandy');
INSERT INTO `artikel` VALUES (5, 'E07222', 'aaa-ccc-111', 'ficelle', '34', 'Mandy');
INSERT INTO `artikel` VALUES (6, 'E07664', 'bbb-aaa-333', 'blanc', 'T2', 'Anouch');

Der code für die Auftragspositionstabelle:
--
-- Tabellenstruktur für Tabelle `artik_pos`
--

CREATE TABLE `artik_pos` (
`id` int(10) NOT NULL auto_increment,
`artnr` varchar(255) NOT NULL default '',
`artid` varchar(255) NOT NULL default '',
`bez1` varchar(255) NOT NULL default '',
`bez2` varchar(255) NOT NULL default '',
`look` varchar(255) NOT NULL default '',
`menge` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 ;

--
-- Daten für Tabelle `artik_pos`
--

INSERT INTO `artik_pos` VALUES (1, 'E07664', 'bbb-aaa-111', 'Anouch', 'noir, T1', '133', '2');
INSERT INTO `artik_pos` VALUES (2, 'E07664', 'bbb-aaa-222', 'Anouch', 'noir, T2', '133', '1');
INSERT INTO `artik_pos` VALUES (3, 'E07221', 'aaa-bbb-111', 'Manou', 'blanc, 32', '122', '2');
INSERT INTO `artik_pos` VALUES (4, 'E07222', 'aaa-bbb-222', 'Mandy', 'blanc, 34', '110', '2');
INSERT INTO `artik_pos` VALUES (5, 'E07222', 'aaa-ccc-111', 'Mandy', 'ficelle, 34', '110', '1');

Meine Hauptproblem besteht darin, das ich nicht weis wie ich die einzelnen Artikel nach gruppe sortiert, in spalten aufgeteilt nach name, farbe in senkrechter Abfrage und die grösse in waagerechter Abfrage, abfrage. 161

mit der derzeitigen Abfrage klappt es nicht so ganz *omg*:
SELECT
artik_pos.bez1,
artikel.bez2,
artikel.groesse,
(SELECT SUM(artik_pos.menge) FROM artik_pos WHERE artikel.artid = artik_pos.artid) AS menge
FROM artik_pos
LEFT JOIN artikel ON artikel.artid = artik_pos.artid
GROUP BY artik_pos.artid

Heraus kommt:
158

Aussehen sollte die Tabelle aber so:

162

Statt der leeren Felder können auch nullen stehen (für die menge) der name sollte leer bleiben(kann aber auch angezeigt werden, falls mehrere Positionen der gleichen Gruppe aber unterschiedliche id's bestehen).

Irgendwie komm ich damit noch nicht so ganz zurecht. Kann mir jemand sagen wie ich das bewerkstelligen kann? Für Hilfe wäre ich sehr dankbar!

MfG OBC

[Heinrich]

Zeig mal die while-Schleife, mit der du die Tabellenzeilen aufbaust.

Du wirst wohl in die while-Schleife noch eine (z.B. foreach) Schleife einbauen müssen, um die Tabellenspalten zu füllen.

[orangebarcode]

Ich baue die Tabelle mit der mysql abfrage direkt in phpmyadmin auf.

Dazu verwende ich wie oben geschrieben ff. code:

SELECT
artik_pos.bez1,
artikel.bez2,
artikel.groesse,
(SELECT SUM(artik_pos.menge) FROM artik_pos WHERE artikel.artid = artik_pos.artid) AS menge
FROM artik_pos
LEFT JOIN artikel ON artikel.artid = artik_pos.artid
GROUP BY artik_pos.artid

Mit dieser Abfrage wird ff. Tabelle ausgegeben:

158

Ich weis halt nicht wie ich die Abfrage gestalten soll damit er die folgende Tabelle ausgibt!

162

Wenn ich eine foreach schleife einbauen soll dann bitte an welcher Stelle?

[Heinrich]

Mit PHP würde ich das so machen:
echo "<table><tr>";
echo "Überschrift Spalte 1";
// usw.
echo "</tr>";

$daten = "(dein SELECT-Statement)";
while ($zeile = mysql_fetch_object($daten)) {
$bez1 = $zeile->bez1;
// usw.

echo "<tr>";

foreach ($zeile as $spaltenwert) {
echo "<td>".$spaltenwert."</td>";
}
echo "</tr>";
}
echo "</table>";
Auf die Schnelle und ungestestet. Wenn's Probleme gibt, meld' dich nochmal.

[orangebarcode]

Ich will aber keine Abfrage in PHP machen!!!!!!

Ich sagte doch schon! ich brauch so eine Abfrage direkt in mysql!! da ich keine index.php o.ä. nutze! Ich besitze halt ein Warenwirtschaftssystem wo man seine eigenen Lieferscheine und Rechnungen selber gestalten kann! (z.B. mit CrystalReports).

Da das WWS local auf einem Server liegt, und mit Microsoft MYSQL Server 2005 ausgestattet ist, kann ich ganz normale mysql abfragen erstellen! Die ausgabe erfolgt irgendwie automatisch!

D.h. im Klartext: ich habe nicht die Möglichkeit solche php-schleifen einzubauen, da ich ja auch nicht mit php die Datenbank abrufe! Das macht das WWS von allein! Ich muss ihm nur die Anweisung dafür geben! Dafür gibt es extra ein System was dir solche abfragen erleichtern soll! Dort wird alles(soweit ich es gesehen hab) mit mysqlabfragen erstellt, ohne das irgendwo ein echo wert, o.ä. existiert. Mir bleibt halt nix anderes übrig als die Abfrage so zu erstellen, das eine vernünftige Kreuztabelle ausgegeben wird!

Da ich für jemanden das WWS pflege, hab ich es natürlich nicht bei mir daheim. Daher hab ich in phpMyAdmin die Abfragen gemacht
(also in einer neu erstellten Datenbank s.o.). Dort gibst du den SQL-Query ins SQL-einfügen-Fenster ein. Ohne irgendwelche phpfunktionen!
sobald du einen SQL-Query absendest, erhältst du eine Tabellenausgabe die deinem SQL-Query entspricht!

Dazu auch siehe 163

Ich hoffe das Problem wurde verstanden! ;)

[Heinrich]

Okay - ich denke, du musst dein Subselect anders aufbauen:
(SELECT SUM(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "T1") AS T1,
(SELECT SUM(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "T2") AS T2,
// usw.
Bin nicht sicher, aber es könnte klappen...

[orangebarcode]

Okay, das sieht auf den ersten blick passabel aus. Werd ich gleich mal testen!

Hoffe das mich das ein wenig weiter bringt.

[orangebarcode]

Ok. Soweit so gut! Das war im übrigen eine sehr gute idee! Danke

nur jetz hab ich ff. Problem!

Bei dieser Abfrage

SELECT artik_pos.bez1, artikel.bez2,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "32") AS Gr32,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "34") AS Gr34,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "38") AS Gr38,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "42") AS Gr42,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "T2") AS T2,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = "T1") AS T1
FROM artik_pos
LEFT JOIN artikel ON artikel.artid = artik_pos.artid
GROUP BY artik_pos.artid ORDER BY artik_pos.bez1

gibt er in den Zeilen wo die Menge drin steht eine 5 aus. Und das bei jedem Datensatz!
Wenn ich statt COUNT(), SUM() verwende bekomme ich überall 8!
Wenn ich nur artik_pos.menge einsetze, gibt mysql einen syntax error aus!
Ich verzweifel gleich!

Wie soll ich das bitte deuten?

Ansonsten ist die Tabelle ganz nach meinen Wünschen! Mercy

[Heinrich]

Zeig mal, wie die Tabelle artikel aussieht (in deinem ersten Posting hast du 2x die Tabelle artik_pos dargestellt).

[orangebarcode]

Zeig mal, wie die Tabelle artikel aussieht (in deinem ersten Posting hast du 2x die Tabelle artik_pos dargestellt).

Schau mal genau hin! Das erste Bild heisst artikel.jpg und das zweite artik_pos.jpg! ;D

Die beiden Tabellen ähneln sich auf den ersten Blick!
Beim zweiten mal draufschauen wirst du feststellen, das in der artik_pos tabelle alle spalten aus der artikel tabelle übernommen worden sind.
Mit einem Unterschied (und der war halt ausschlaggebend, warum ich eine verdammte Kreuztabelle erstellen muss), dass die Farbe sowie die Grösse zu einer Spalte zusammengefasst werden (kann ich auch nicht ändern, da ich kein einfluss auf die programmierung des WWS nehmen kann), so dass es sich sehr schwer nach farbe und grösse zuordnen lässt. Daher brauch ich ja diese kreuztabelle!

-----
Edit:
Ach sorry, hab jetz erst gesehen das die codes dieselben sind. ^^

Ok. hier der code für die Artikel tabelle:

--
-- Tabellenstruktur für Tabelle `artikel`
--

CREATE TABLE `artikel` (
`id` int(11) NOT NULL auto_increment,
`artnr` varchar(255) NOT NULL default '',
`artid` varchar(255) NOT NULL default '',
`bez2` varchar(255) NOT NULL default '',
`groesse` varchar(255) NOT NULL default '',
`bez1` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 ;

--
-- Daten für Tabelle `artikel`
--

INSERT INTO `artikel` VALUES (1, 'E07664', 'bbb-aaa-111', 'noir', 'T1', 'Anouch');
INSERT INTO `artikel` VALUES (2, 'E07664', 'bbb-aaa-222', 'noir', 'T2', 'Anouch');
INSERT INTO `artikel` VALUES (3, 'E07221', 'aaa-bbb-111', 'blanc', '32', 'Manou');
INSERT INTO `artikel` VALUES (4, 'E07222', 'aaa-bbb-222', 'blanc', '34', 'Mandy');
INSERT INTO `artikel` VALUES (5, 'E07222', 'aaa-ccc-111', 'ficelle', '34', 'Mandy');
INSERT INTO `artikel` VALUES (6, 'E07664', 'bbb-aaa-333', 'blanc', 'T2', 'Anouch');

[Heinrich]

Ich meinte den Tabellencode im ersten Posting - das ist 2x die artik_pos.

COUNT() gibt die Anzahl der betreffenden Datensätze wieder - ist also falsch, um die Mengen zu addieren (hatte ich anfangs übersehen).
SUM() addiert die Werte aus den betreffenden Feldern - 8 ist die Gesamtzahl deiner Waren. Also greift so die WHERE-Bedingung nicht.

Kann jetzt nur raten:
Mach' mal einen einfachen JOIN (kein LEFT JOIN)
oder
Mach mal GROUP BY artikel.groesse

[orangebarcode]

Habe nochmal die richtige tabelle gepostet und im ersten post die tabelle geändert!

COUNT() ist eigentlich nicht schlecht. da ich ja die menge gar nicht addieren will, was ich anfangs übersehen habe!
habe diesen syntax um COUNT(distinct...) erweitert

Jetz bekomm ich überall den höchsten wert in der spalte menge ausgegeben!
Also wenn irgendwo eine 3 z.b. als menge angegeben ist steht überall ne 3.

ich versuch mal deinen tip jetz nochmal mitm join, und werd dann mal weitersehen!

Danke erstmal bis hierhin für deine hilfe, hatte bis vor einer woche noch gar keinen blassen schimmer von kreuztabellen! Langsam steig ich durch auch wenns ziemlich heftig ist, da man in tausend richtungen denken muss. in php fand ich für solche dinge ne einfachere lösung! Aber man lernt ja nie aus!

[orangebarcode]

ok. habs mal mit join versucht. immer noch dasselbe ergebnis!

SELECT artik_pos.bez1, artikel.bez2, look,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = 'T1') AS T1,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = 'T2') AS T2,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '32') AS Gr32,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '34') AS Gr34,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '38') AS Gr38,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '42') AS Gr42
FROM artik_pos
JOIN artikel ON artikel.artid = artik_pos.artid
ORDER BY artikel.groesse

kommt wieder überall 5 heraus.

SELECT artik_pos.bez1, artikel.bez2, look,
(SELECT COUNT(distinct artik_pos.menge) FROM artik_pos WHERE artikel.groesse = 'T1') AS T1,
(SELECT COUNT(distinct artik_pos.menge) FROM artik_pos WHERE artikel.groesse = 'T2') AS T2,
(SELECT COUNT(distinct artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '32') AS Gr32,
(SELECT COUNT(distinct artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '34') AS Gr34,
(SELECT COUNT(distinct artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '38') AS Gr38,
(SELECT COUNT(distinct artik_pos.menge) FROM artik_pos WHERE artikel.groesse = '42') AS Gr42
FROM artik_pos
JOIN artikel ON artikel.artid = artik_pos.artid
ORDER BY artik_pos.bez1

und hier wieder der höchste wert in der spalte menge...

Ich weis echt nich mehr weiter!

[meikel (†)]

ok. habs mal mit join versucht. immer noch dasselbe ergebnis!

SELECT artik_pos.bez1, artikel.bez2, look,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = 'T1') AS T1,
(SELECT COUNT(artik_pos.menge) FROM artik_pos WHERE artikel.groesse = 'T2') AS T2,
...

Wie wärs damit:
SELECT artik_pos.bez1, artikel.bez2, look,
(SELECT IF(COUNT(*)>0, 'T1', NULL) FROM artik_pos WHERE artikel.groesse = 'T1') AS T1,
...

http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html
IF(expr1,expr2,expr3)
IFNULL(expr1,expr2)

[orangebarcode]

Hmm,

SELECT artik_pos.bez1, artikel.bez2, look,
(SELECT IF(COUNT(*)>0, 'T1', NULL) FROM artik_pos WHERE artikel.groesse = 'T1') AS T1,
(SELECT IF(COUNT(*)>0, 'T2', NULL) FROM artik_pos WHERE artikel.groesse = 'T2') AS T2,
(SELECT IF(COUNT(*)>0, 'Gr32', NULL) FROM artik_pos WHERE artikel.groesse = '32') AS Gr32,
(SELECT IF(COUNT(*)>0, 'Gr34', NULL) FROM artik_pos WHERE artikel.groesse = '34') AS Gr34,
(SELECT IF(COUNT(*)>0, 'Gr38', NULL) FROM artik_pos WHERE artikel.groesse = '38') AS Gr38,
(SELECT IF(COUNT(*)>0, 'Gr42', NULL) FROM artik_pos WHERE artikel.groesse = '42') AS Gr42
FROM artik_pos
LEFT JOIN artikel ON artikel.artid = artik_pos.artid
ORDER BY artikel.bez1

Das einzige was jetzt passiert, ist das statt der menge genau die Grössenangabe steht die in der if schleife deklariert wird!

SELECT artik_pos.bez1, artikel.bez2, look,
(SELECT IF(COUNT(distinct artik_pos.menge)>0, artik_pos.menge, '0') FROM artik_pos WHERE artikel.groesse = 'T1') AS T1,
(SELECT IF(COUNT(distinct artik_pos.menge)>0, artik_pos.menge, '0') FROM artik_pos WHERE artikel.groesse = 'T2') AS T2,
(SELECT IF(COUNT(distinct artik_pos.menge)>0, artik_pos.menge, '0') FROM artik_pos WHERE artikel.groesse = '32') AS Gr32,
(SELECT IF(COUNT(distinct artik_pos.menge)>0, artik_pos.menge, '0') FROM artik_pos WHERE artikel.groesse = '34') AS Gr34,
(SELECT IF(COUNT(distinct artik_pos.menge)>0, artik_pos.menge, '0') FROM artik_pos WHERE artikel.groesse = '38') AS Gr38,
(SELECT IF(COUNT(distinct artik_pos.menge)>0, artik_pos.menge, '0') FROM artik_pos WHERE artikel.groesse = '42') AS Gr42
FROM artik_pos
LEFT JOIN artikel ON artikel.artid = artik_pos.artid
ORDER BY artik_pos.bez1

Komischerweise steht jetzt überall eine 2.

Hab die Mengenangaben mal in der artik_pos geändert. und zwar jedem artikel eine andere menge. also 1,2,3,4,5.
Ok. Abfrage mit dem letzen Query ergibt überall die 1, da der erste eintrag in der artik_pos tabelle mit menge 1 eingetragen ist! steht beim ersten eintrag ne 6 steht auch überall eine 6 drin. Der Syntax COUNT(distinct...) kann auch COUNT(...) sein, das Ergbnis bleibt dasselbe!

164

Langsam geht mir echt die Luft aus!

[orangebarcode]

Hat den keiner ne Ahnung wie ich die Spalte menge so abfragen kann, daß auch die reale Menge herauskommt! Und nicht nur immer der erste Datensatz der Spalte menge bzw. irgend eine unerklärliche Summe dessen.

Es muss doch eine lösung geben!

[Heinrich]

Nochmal: COUNT() zählt die Anzahl der Datensätze. Du willst aber doch die Spalte Menge in der artik_pos auslesen und die Summe erhalten >>> also SUM()!!

Dass das in php einfacher geht, sagte ich schon. Aber das kannst du wohl nicht machen, wenn ich dich richtig verstanden habe.

Übrigens ist die Struktur dieser zwei Quelltabellen bescheuert. Es macht keinen Sinn, gleiche Felder in zwei Tabellen zu pflegen. Ich weiß, dass es nicht deine sind. Aber man kann einem Auftraggeber auch sagen, dass es so nicht geht.

[orangebarcode]

Ja toll, aber SUM() rechnet alle Datensätze der Spalte menge zusammen! Wie kann ich da jetzt nur die jeweilige menge ausgeben lassen? Das macht irgendwie alles keinen Sinn! Ohne Syntax kommt ein Syntax error.


Übrigens ist die Struktur dieser zwei Quelltabellen bescheuert. Es macht keinen Sinn, gleiche Felder in zwei Tabellen zu pflegen. Ich weiß, dass es nicht deine sind. Aber man kann einem Auftraggeber auch sagen, dass es so nicht geht.

Es macht Sinn, da in der eigentlichen noch mehr Spalten existieren. Ich habe bloss einen Teil der Struktur nachempfunden, da dies der wesentliche Teil ist auf den ich mich konzentrieren muss!


Dass das in php einfacher geht, sagte ich schon. Aber das kannst du wohl nicht machen, wenn ich dich richtig verstanden habe.

Wenn ich das Problem in php gehabt hätte wärs ja kein Problem... O.o (komischer Satz ^^)

[Heinrich]

SUM() rechnet die Werte jener Datensätze zusammen, die du mit der WHERE-Bedingung selektierst.

[orangebarcode]

Und wieso gibt er mir dann immer die komplette Summe der spalte menge aus?
D.h. dann ja, da wäre die where bedingung FALSCH! Und das würde bedeuten, dass jemand hier etwas falsches gepostet hat...
Und mich noob damit auf den irrweg geschickt hat. ^^

Davon mal abgesehen, wie soll ich aber ne abfrage mache um die idn & die grösse miteinander zuverknüpfen. Oder denk ich da grad wirres Zeuch?? *am-kopf-kratz*

[Malte_HH]

Habe mich auch kürzlich mit Kreuztabellen beschäftigen müssen, probier es mal mit folgender Abfrage (weitere Größen entsprechend eintragen):


SELECT
artikel.bez1, artikel.bez2,
sum(CASE groesse WHEN 'T1' THEN menge ELSE 0 END) as T1,
sum(CASE groesse WHEN 'T2' THEN menge ELSE 0 END) as T2,
sum(CASE groesse WHEN 'T3' THEN menge ELSE 0 END) as T3,
sum(CASE groesse WHEN '32' THEN menge ELSE 0 END) as G32,
sum(CASE groesse WHEN '34' THEN menge ELSE 0 END) as G34,
sum(CASE groesse WHEN '38' THEN menge ELSE 0 END) as G38,
sum(CASE groesse WHEN '42' THEN menge ELSE 0 END) as G42
FROM artik_pos
LEFT JOIN artikel ON artikel.artid = artik_pos.artid
GROUP BY
artikel.artnr, artikel.bez2


Das Ergebnis der Abfrage habe ich als Anlage beigefügt.

[orangebarcode]

ALDER, ICH FASS ES NICHT!

EIN HOCH AUF MALTE!!

Das ist ja echt der Wahnsinn, genau die Tabelle die ich haben wollte, HUT AB! Und das gewisse Detail, nämlich die Artikel zu ordnen die gleich sind aber wo die Grössen unterschiedlich sind.

Wärst du jetzt ne Frau, würd ich dich küssen dafür!

Aber so gibts nur ein Phattes Lob!
Danke für deine riesen Hilfe!

Thema damit abgeschlossen!