mySQL: zweidimensionale Abfrage

[lixx]

Hallo Leute!

Nach längerem Suchen muss ich annehmen, dass der Bergiff "zweidimensionale Abfrage" nicht dem entspricht, was ich suche. Daher bitte ich euch mir bei meinem Problem zu helfen:

Was ich haben will ist eine Tabellen-Darstellung wo die horizontale und die vertikale Seite jeweis in Verbindung zueinander stehen. Also A1 zu A2 = 1, A1 zu A4 = 2 usw. Das sollte so aussehen:

|  |A1|A2|A3|A4|
|A1| 0| 1| 0| 2|
|A2| 1| 0| 2| 0|
|A3| 0| 2| 0| 1|
|A4| 2| 0| 1| 0|

Jetzt habe ich dafür eine Tabelle (Tab) mit den An-Elementen und eine Beziehungstabelle Tab_mm:

Tab:
id int(4) unsigned NOT NULL auto_increment,
name char(32) collate utf8_bin NOT NULL default '',

Tab_mm:
tab1\_id1 int(4) unsigned NOT NULL default '0',
tab1\_id2 int(4) unsigned NOT NULL default '0',
status tinyint(1) unsigned NOT NULL default '0',

Es wird nur dann ein Datensatz in Tab_mm angelegt, wenn der Status != 0 ist.

Wie kann ich nun eine mySQL-Abfrage machen, die mir die Ausgabe gleich mit allen benötigten Daten ausgibt? Mit z.B. ...

SELECT Tab.id AS id1, Tab.name,
Tab_mm.tab1_id2 AS id2, Tab_mm.status
FROM Tab
JOIN Tab_mm
ORDER BY name

... kommt dem zwar schon nahe, ist aber noch nicht ganz das was ich will.

Wüschenswert wäre eine Ausgabe, die ALLE Datensätze, auch die ohne mm-Datensatz, ausgibt. Wie etwa:

name|id1|id2|status
 A1 |  1|  1|NULL
 A1 |  1|  2| 1
 A1 |  1|  3|NULL
 A1 |  1|  4| 2
 A2 |  2|  1| 1
 A2 |  2|  2|NULL
 A2 |  2|  3|NULL
 A2 |  2|  4|NULL
 A3 |  3|  1|NULL
 A3 |  3|  2|NULL
 A3 |  3|  3|NULL
 A3 |  3|  4|NULL
 A4 |  4|  1| 2
 A4 |  4|  2|NULL
 A4 |  4|  3|NULL
 A4 |  4|  4|NULL

Geht das irgendwie ohne PHP-Schleife?

Bin für jede Hilfe dankbar!

lg lixx

[Ilja]

yo,

Jetzt habe ich dafür eine Tabelle (Tab) mit den An-Elementen und eine Beziehungstabelle Tab_mm:

Tab:
id int(4) unsigned NOT NULL auto_increment,
name char(32) collate utf8_bin NOT NULL default '',

ich sehe die An-Elemente nicht, nur zwei spalten bestehend aus einer id und einer spalte 'name'. du willst doch nicht etwa die unterschiedlichen werte alle in eine spalte packen oder ? ;-)

des weiteren sind die namen deiner tabellen unglücklich gewählt, man kann nicht die fachlichkeit daraus lesen.

deine abfrage ist ein outer join, aber bevor du dich daran machst, solltst du eventuell noch mal dein design überdenken.  sieht jedenfalls auf den ersten blick noch verbesserungswürdig aus.

Ilja

[lixx]

Danke für Deine Antwort Ilja!

Jetzt habe ich dafür eine Tabelle (Tab) mit den An-Elementen und eine Beziehungstabelle Tab_mm:

Tab:
id int(4) unsigned NOT NULL auto_increment,
name char(32) collate utf8_bin NOT NULL default '',

ich sehe die An-Elemente nicht, nur zwei spalten bestehend aus einer id und einer spalte 'name'. du willst doch nicht etwa die unterschiedlichen werte alle in eine spalte packen oder ? ;-)

An-Elemente sind die A1, A2 ect. Das ist eigentlich der Name. Also: Spalte 'name'. Und die Verknüpfungen sind in der Tabelle Tab_mm.

des weiteren sind die namen deiner tabellen unglücklich gewählt, man kann nicht die fachlichkeit daraus lesen.

Die Tabellennamen sind anonymisiert (Vorschrift). Könnte auch gemüse und gemüse_mm heissen ;-)

deine abfrage ist ein outer join, ...

Habe nun selbst eine Lösung hinbekommen. Vll wirfst Du auch noch einmal einen Blick darauf. Auf jeden Fall sollte man nicht eindimensional denken, wenn man eine zweidimensionale Abfrage möchte *gg*

SELECT Tab.id AS Tab_id1, Tab.name, Tab_mm.Tab_id2, Tab_mm.status
FROM Tab
LEFT JOIN Tab_mm
 ON ( Tab_mm.Tab_id1 = Tab.id )
RIGHT JOIN Tab as Tab2
 ON ( Tab2.id = Tab_mm.Tab_id2 )
ORDER BY Tab.name, Tab2.id

So bekomme ich auch die Ergebnisse, die keine Relation in der Tab_mm haben.

lg lixx

[Ilja]

yo,

So bekomme ich auch die Ergebnisse, die keine Relation in der Tab_mm haben.

wenn ich höre, dass immer wieder der begriff relation als beziehung benutzt wird, dann bekomme ich plug ;-)

eine relation ist eine tabellen, relationships sind beziehungen in rdbms...

Ilja

[lixx]

wenn ich höre, dass immer wieder der begriff relation als beziehung benutzt wird, dann bekomme ich plug ;-)

Interessant was Wikipedia für plug zur Auswahl hat ;)
http://de.wikipedia.org/wiki/Plug

eine relation ist eine tabellen, relationships sind beziehungen in rdbms...

In Begriffen war ich nie eine Eins. Hauptsache das Ergebnis stimmt. Aber danke für den Versuch mir das beizubringen. Ein ganz hoffnungsloser Fall bin ich dann auch noch nicht ;)

lg lixx

[Norbert]

Hallo,

das war mir alles zu unkonkret, also habe ich mal ein praktisches Beispiel gebastelt:

[lang code=sql]CREATE TABLE mx_name (
  id     int(11) NOT NULL auto_increment,  # id-Name
  name   varchar(11) NOT NULL default '',  # Name
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE mx_status (
  sid    int(11) NOT NULL auto_increment,  # id-Status
  status varchar(11) NOT NULL default '',  # Status
  PRIMARY KEY (sid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE mx_matrix (
  idV  int(11) NOT NULL default 0,       # id-horizontal
  idH  int(11) NOT NULL default 0,       # id-vertikal
  idS  int(11) NOT NULL default 0,       # id-Status
  UNIQUE (idV,idH)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO mx_name VALUES
 (1, 'Agnes'), (2, 'Bernd'), (3, 'Claus'),
 (4, 'Dörte'), (5, 'Emil'), (6, 'Frank');

INSERT INTO mx_status VALUES
 (1, 'verheiratet'), (2, 'geschieden'), (3, 'verfeindet'),
 (4, 'Geschwister'), (5, 'verlobt'), (6, 'verliebt');

INSERT INTO mx_matrix VALUES
 (1, 2, 2), (2, 3, 4), (3, 4, 6),
 (4, 5, 1), (5, 6, 3), (6, 1, 5);

SELECT b.name, c.name, d.status
     FROM mx_matrix AS a
LEFT JOIN mx_name   AS b ON a.idH = b.id
LEFT JOIN mx_name   AS c ON a.idV = c.id
LEFT JOIN mx_status AS d ON a.idS = d.sid;

erzeugt:

+-------+-------+-------------+
| name  | name  | status      |
+-------+-------+-------------+
| Bernd | Agnes | geschieden  |
| Claus | Bernd | Geschwister |
| D÷rte | Claus | verliebt    |
| Emil  | D÷rte | verheiratet |
| Frank | Emil  | verfeindet  |
| Agnes | Frank | verlobt     |
+-------+-------+-------------+
6 rows in set (0.00 sec)[/code]

Das kannst Du jetzt nach belieben aufbohren ...
HTH

Gruss und Dank
Norbert

[lixx]

Hallo Norbert!

Also für diese Antwort gebührt Dir einmal ein extra Dankeschön!

Das mit der Matrix ist natürlich ganz gefinkelt. Aber noch einmal eine extra Tabelle anlegen ... ich weiß nicht. Ich hoffe dass die Variante, die ich mittlerweile herausgefunden habe auch funktioniert:

SELECT Tab.id AS Tab_id1, Tab.name, Tab_mm.Tab_id2, Tab_mm.status
FROM Tab
LEFT JOIN Tab_mm
 ON ( Tab_mm.Tab_id1 = Tab.id )
RIGHT JOIN Tab as Tab2
 ON ( Tab2.id = Tab_mm.Tab_id2 )
ORDER BY Tab.name, Tab2.id

Ist zwar etwas flapsig aber im ersten Test bekomme ich auf jedenfall die Daten so wie ich sie gerne hätte:

name|id1|id2|status
 A1 |  1|  1|NULL     (A1 => A1)
 A1 |  1|  2| 1       (A1 => A2)
 A1 |  1|  3|NULL     (A1 => A3)
 A1 |  1|  4| 2       (A1 => A4)
 A2 |  2|  1| 1       (A2 => A1)
 A2 |  2|  2|NULL     (A2 => A2)
 A2 |  2|  3|NULL     ...
 A2 |  2|  4|NULL
 A3 |  3|  1|NULL
 A3 |  3|  2|NULL
 A3 |  3|  3|NULL
 A3 |  3|  4|NULL
 A4 |  4|  1| 2
 A4 |  4|  2|NULL
 A4 |  4|  3|NULL
 A4 |  4|  4|NULL

lg lixx