SQL-Knobelei

[Fritze]

Guten Morgen,

mit einem Skript wäre die Aufgabe zweifelsohne längst erledigt, aber da ich bis jetzt alles per SQL gelöst habe, hat mich hat der Ehrgeiz gepackt: Es sollen Daten aus drei alten Tabellen in vier neue übertragen werden, es geht nur noch im die letzte, und auch da nur noch um eine Spalte.

Alte Tabelle:
Gegeben sei eine Lagerliste, in der für jeden eingelagerten Artikel ein Datensatz existiert:

alt.produktname
alt.bestellnummer
alt.regalnummer
alt.fachnummer

.fachnummer hat die unangenehme Besonderheit, in jedem Regal existieren zu können, da die Fächer jedes Regals immer ab 1 durchnummeriert wurden. Alleine aus .fachnummer lässt sich der Lagerort also nicht bestimmen.
Obendrein kann .fachnummer auch unbekannt (NULL) sein, der Lagerist war dann wieder zu faul, das Produkt steht im Gang.

.produktname ist immer eingetragen, .bestellnummer jedoch nicht. Die Kombination .produktname & .bestellnummer führt bei den neuen Tabellen zu einem Produkteintrag produkt.id; .produktname="Bla" & .bestellnummer=42 hätte also produkt.id 1, .produktname="Bla" & .bestellnummer=NULL hätte produkt.id 2. Der gleiche .produktname kann auch unterschiedliche .bestellnummern haben.

Bereits erfolgreich gefüllte neue Tabellen:

regal.id  = Seriennummer, neu vergeben, eindeutig
regal.alt = alt.regalnummer

fach.id    = Seriennummer, neu vergeben, eindeutig
fach.alt   = alt.fachnummer
fach.regal = regal.id

produkt.id   = Seriennummer, neu vergeben, eindeutig
produkt.name = alt.produktname
produkt.alt  = alt.bestellnummer

Das Problemkind:

neu.produktid = produkt.id (-> produkt.alt -> alt.bestellnummer)
neu.regalid   = regal.id   (-> regal.alt   -> alt.regalnummer)
neu.fachid    = fach.id    (-> fach.alt    -> alt.fachnummer)
neu.info      = Teil aus alt.produktname

Für die "insert into … select"-Anweisung habe ich bislang folgendes zusammengeknobelt:

select teil(alt.produktname),produkt.id,regal.id from regal  
  right join alt on regal.alt=alt.regalnummer  
  left join produkt on alt.produktname=produkt.titel and if(alt.bestellnummer is not null,alt.bestellnummer=produkt.alt,true);

Das scheint auch zu funktionieren, jedoch bekomme ich die neue Fachnummer da nicht unter. Ich habe den Verdacht, dass es mit einem weiteren join nichts werden wird und ich mit einer Hilfstabelle arbeiten muss.

Wer kann helfen?

PS: Man möge mich bitte nicht nach der if-Klausel fragen; es geht um die Produktnamen, die mit unterschiedlichen Bestellnummern auftreten, aber die tatsächliche Funktion kann ich im Moment nur mit "Einfälle im Dunkeln der Nacht" erklären.

[Vinzenz Mai]

Hallo,

Wer kann helfen?

Du.

Ich verstehe die einzelnen Worte Deines Beitrags. Ich verstehe sogar, dass Du Daten aus einer alten Tabelle in eine neue übertragen willst. Ich kann leider Deine Problembeschreibung überhaupt nicht nachvollziehen. Ich verstehe beim besten Willen nicht, wie die Daten vorher aussehen und wie sie nachher aussehen sollen.

Dabei könnte es so einfach sein:
Ein paar Beispieldatensätze in der alten Tabelle, hübsch tabellarisch, und die daraus resultierenden gewünschten Datensätze für die neue Tabelle - mit der Begründung, warum die Daten so aussehen sollen.

Freundliche Grüße

Vinzenz

[Fritze]

Dabei könnte es so einfach sein:

Oha. Dann nehme ich Dich mal beim Wort.

Ein paar Beispieldatensätze in der alten Tabelle, hübsch tabellarisch, und die daraus resultierenden gewünschten Datensätze für die neue Tabelle

Alte Tabelle:

(Ich habe alt.bestellnummer in alt.produktnummer umbenannt.)

alt.produktname  alt.produktnummer  alt.regalnummer  alt.fachnummer
Knüsperdü        23                 1                2
Knüsperdü                           3                8
Pustekuh                            5                2
Rattelfix        42                 5
Sausemaus                           1                4
Sausemaus                           3                6
Sausemaus                           3

Neue Tabellen:

Regale
------
regal.id: Neue Seriennummer
regal.alt = alt.regalnummer

regal.id  regal.alt  (weitere Spalten ohne Belang)
1         1
2         3
3         5

Wir haben in dieser Tabelle also die Zuordnung Alte Regalnummer (regal.alt) - Neue Regalnummer (regal.id).

Fächer
------
fach.id: Neue Seriennummer (jetzt global eindeutig, nicht nur pro Regal)
fach.alt = alt.fachnummer
fach.regal <-> regal.id

fach.id  fach.alt  fach.regal  (weitere Spalten ohne Belang)
1        2         1
2        4         1
3        8         2
4        2         3
5        6         2

Auch in dieser Tabelle wieder die Zuordnung Alte Fachnummer (fach.alt) - Neue Fachnummer (fach.id). Zusätzlich haben wir die Zuordnung Fachnnummer - Regalnummer.

Produktdaten
------------
produkt.id   = Neue Seriennummer (jetzt für alle Produkte ein Eintrag)
produkt.name = alt.produktname
produkt.alt  = alt.produktnummer

produkt.id  produkt.name  produkt.alt  (weitere Spalten ohne Belang)
1           Knüsperdü     23
2           Knüsperdü
3           Pustekuh
4           Rattelfix     42
5           Sausemaus

Diese drei Tabellen, Regale, Fächer und Produktdaten, konnte ich bereits mit je einer join/select-Anweisung befüllen. Es geht jetzt darum, dieses Kunststück auch mit der vierten Tabelle zu vollbringen, so überhaupt möglich:

Lagerbestand
------------
neu.produktid <-> produkt.id (-> produkt.alt = alt.produktnummer)
neu.regalid   <-> regal.id   (-> regal.alt   = alt.regalnummer)
neu.fachid    <-> fach.id    (-> fach.alt    = alt.fachnummer)
neu.info      = Teil aus alt.produktname

neu.produktid  neu.regalid  neu.fachid  neu.info
1              1            1           Knüsperdü
2              2            3           Knüsperdü
3              3            4           Pustekuh
4              3                        Rattelfix
5              1            2           Sausemaus
5              2            5           Sausemaus
5              2                        Sausemaus

PS: Bitte nicht damit aufhalten, die Tabellenstruktur zu überarbeiten - das soll so sein. Mir ist bewusst, dass Beispiel bei der scheinbar / teils überflüssigen neu.regalid-Spalte hinkt. Aber: Es geht nur um das, was ich bereits eingangs und im Titel angesprochen habe.

[Fritze]

Problem ist gelöst, eine Verständnisfrage habe ich aber dennoch:

select * from alt  
  left join produkt on alt.name=produkt.name and if(alt.nummer is null,produkt.alt is null,alt.nummer=produkt.alt)  
  left join regal on alt.regal=regal.alt  
  left join fach on alt.fach=fach.alt and regal.id=fach.regal;

| alt                               | produkt                 | regal       | fach
+-----------+--------+-------+------+------+-----------+------+------+------+------+------+-------+
| name      | nummer | regal | fach | id   | name      | alt  | id   | alt  | id   | alt  | regal |
+-----------+--------+-------+------+------+-----------+------+------+------+------+------+-------+
| Knüsperdü |     23 |     1 |    2 |    1 | Knüsperdü |   23 |    1 |    1 |    1 |    2 |     1 |
| Knüsperdü |   NULL |     3 |    8 |    2 | Knüsperdü | NULL |    2 |    3 |    3 |    8 |     2 |
| Pustekuh  |   NULL |     5 |    2 |    3 | Pustekuh  | NULL |    3 |    5 |    4 |    2 |     3 |
| Rattelfix |     42 |     5 | NULL |    4 | Rattelfix |   42 |    3 |    5 | NULL | NULL |  NULL |
| Sausemaus |   NULL |     1 |    4 |    5 | Sausemaus | NULL |    1 |    1 |    2 |    4 |     1 |
| Sausemaus |   NULL |     3 |    6 |    5 | Sausemaus | NULL |    2 |    3 |    5 |    6 |     2 |
| Sausemaus |   NULL |     3 | NULL |    5 | Sausemaus | NULL |    2 |    3 | NULL | NULL |  NULL |
+-----------+--------+-------+------+------+-----------+------+------+------+------+------+-------+

select * from alt  
  left join produkt on alt.name=produkt.name and alt.nummer=produkt.alt  
  left join regal on alt.regal=regal.alt  
  left join fach on alt.fach=fach.alt and regal.id=fach.regal;

| alt                               | produkt                 | regal       | fach
+-----------+--------+-------+------+------+-----------+------+------+------+------+------+-------+
| name      | nummer | regal | fach | id   | name      | alt  | id   | alt  | id   | alt  | regal |
+-----------+--------+-------+------+------+-----------+------+------+------+------+------+-------+
| Knüsperdü |     23 |     1 |    2 |    1 | Knüsperdü |   23 |    1 |    1 |    1 |    2 |     1 |
| Knüsperdü |   NULL |     3 |    8 | NULL | NULL      | NULL |    2 |    3 |    3 |    8 |     2 |
| Pustekuh  |   NULL |     5 |    2 | NULL | NULL      | NULL |    3 |    5 |    4 |    2 |     3 |
| Rattelfix |     42 |     5 | NULL |    4 | Rattelfix |   42 |    3 |    5 | NULL | NULL |  NULL |
| Sausemaus |   NULL |     1 |    4 | NULL | NULL      | NULL |    1 |    1 |    2 |    4 |     1 |
| Sausemaus |   NULL |     3 |    6 | NULL | NULL      | NULL |    2 |    3 |    5 |    6 |     2 |
| Sausemaus |   NULL |     3 | NULL | NULL | NULL      | NULL |    2 |    3 | NULL | NULL |  NULL |
+-----------+--------+-------+------+------+-----------+------+------+------+------+------+-------+

Die beiden Befehle unterscheiden sich in

if(alt.nummer is null,produkt.alt is null,alt.nummer=produkt.alt)
gegenüber
  alt.nummer=produkt.alt

Warum gibt es bei der ersten Variante Daten bei allen produkt.id-Zeilen, bei der zweiten jedoch nur dort, wenn alt.nummer nicht NULL ist?

Datenbank ist MySQL 5.0.

Quelltabellen:
--------------

alt
+-----------+--------+-------+------+
| name      | nummer | regal | fach |
+-----------+--------+-------+------+
| Knüsperdü |     23 |     1 |    2 |
| Knüsperdü |   NULL |     3 |    8 |
| Pustekuh  |   NULL |     5 |    2 |
| Rattelfix |     42 |     5 | NULL |
| Sausemaus |   NULL |     1 |    4 |
| Sausemaus |   NULL |     3 |    6 |
| Sausemaus |   NULL |     3 | NULL |
+-----------+--------+-------+------+

regal
+------+------+
| id   | alt  |
+------+------+
|    1 |    1 |
|    2 |    3 |
|    3 |    5 |
+------+------+
regal.id: Neue Seriennummer
regal.alt = alt.regalnummer

fach
+------+------+-------+
| id   | alt  | regal |
+------+------+-------+
|    1 |    2 |     1 |
|    2 |    4 |     1 |
|    3 |    8 |     2 |
|    4 |    2 |     3 |
|    5 |    6 |     2 |
+------+------+-------+
fach.id: Neue Seriennummer (jetzt global eindeutig, nicht nur pro Regal)
fach.alt = alt.fachnummer
fach.regal <-> regal.id

produkt
+------+-----------+------+
| id   | name      | alt  |
+------+-----------+------+
|    1 | Knüsperdü |   23 |
|    2 | Knüsperdü | NULL |
|    3 | Pustekuh  | NULL |
|    4 | Rattelfix |   42 |
|    5 | Sausemaus | NULL |
+------+-----------+------+
produkt.id   = Neue Seriennummer (jetzt für alle Produkte ein Eintrag)
produkt.name = alt.produktname
produkt.alt  = alt.produktnummer

Befehle zum Erzeugen der Probiertabellen:
-----------------------------------------

create table alt (name varchar(32),nummer int,regal int, fach int);
create table regal (id int, alt int);
create table fach (id int, alt int, regal int);
create table produkt (id int, name varchar(32), alt int);
create table lager (produkt int, regal int, fach int, name varchar(32));

insert into alt (name,nummer,regal,fach) values ("Knüsperdü",23,1,2), ("Knüsperdü",null,3,8), ("Pustekuh",null,5,2), ("Rattelfix",42,5,null), ("Sausemaus",null,1,4), ("Sausemaus",null,3,6), ("Sausemaus",null,3,null);
insert into regal (id,alt) values (1,1),(2,3),(3,5);
insert into fach (id,alt,regal) values (1,2,1),(2,4,1),(3,8,2),(4,2,3),(5,6,2);
insert into produkt (id,name,alt) values (1,"Knüsperdü",23), (2,"Knüsperdü",null), (3,"Pustekuh",null), (4,"Rattelfix",42), (5,"Sausemaus",null);

[Rouven]

Hello,

Problem ist gelöst, eine Verständnisfrage habe ich aber dennoch:
Warum gibt es bei der ersten Variante Daten bei allen produkt.id-Zeilen, bei der zweiten jedoch nur dort, wenn alt.nummer nicht NULL ist?

ich habs nicht von oben bis unten gelesen sondern antworte mal ins Blaue: Es ist ein Irrglaube, dass
NULL = NULL
true ergibt. NULL = NULL ergibt NULL, ein Record dessen WHERE-Klausel zu NULL evaluiert ist nicht relevant für das Ergebnis.

MfG
Rouven

[Fritze]

Es ist ein Irrglaube, dass NULL = NULL true ergibt. NULL = NULL ergibt NULL

Ah. Da ist jemand gleicher als gleich. Hinterhältig. Danke.