Hallo, ich glaube meine Aufgabenstellung ist ganz einfach, ich krieg’s aber einfach nicht gebacken …
Ich habe zwei Tabellen, die u.a. vergleichbare Daten (z.B. Name und Rufnummer) enthalten. Die möchte ich gerne aneinanderhängen und zusätzlich eine Spalte einfügen, die die Herkunft der Datensätze angibt (z.B. “Tabelle1”, “Tabelle2”).
Jeweils einzeln kriege ich das hin (also entweder aneinanderhängen, oder Spalte einfügen), den SQL-Befehl für die Kombination leider nicht
Nachfolgend mein Code, ich hoffe jemand kann helfen.
Danke,
Rüdiger
[font=Courier New]CREATE TABLE t_local[/font]
[font=Courier New]([/font]
[font=Courier New]name_local text[/font]
[font=Courier New],callno_local text[/font]
[font=Courier New]);[/font]
[font=Courier New]INSERT INTO t_local(name_local, callno_local)[/font]
[font=Courier New]VALUES [/font]
[font=Courier New]('Mueller', '+49 (2371) 90-2148')[/font]
[font=Courier New],('Meier', '+49 (2371) 90-3655')[/font]
[font=Courier New],('Schulze', '+49 (2371) 90-2457')[/font]
[font=Courier New],('Koenig', '+49 (2371) 90-3656')[/font]
[font=Courier New],('Ahne', '+49 (2371) 90-3645')[/font]
[font=Courier New];[/font]
[font=Courier New]CREATE TABLE t_network[/font]
[font=Courier New]([/font]
[font=Courier New]name_network text[/font]
[font=Courier New],callno_network text[/font]
[font=Courier New]);[/font]
[font=Courier New]INSERT INTO t_network(name_network, callno_network)[/font]
[font=Courier New]VALUES [/font]
[font=Courier New]('Skrpek', '+420 (1) 667-2927')[/font]
[font=Courier New],('Plsek', '+420 (1) 667-2579')[/font]
[font=Courier New],('Mrosek', '+420 (1) 667-3641')[/font]
[font=Courier New],('Kretschmar', '+420 (1) 667-2834')[/font]
[font=Courier New],('Musak', '+420 (1) 667-3673')[/font]
[font=Courier New];[/font]
So sehen die Tabellen dann aus:
SELECT * FROM t_local;
[font=Courier New]name_local | callno_local[/font]
[font=Courier New]-----------+--------------------[/font]
[font=Courier New]Mueller | +49 (2371) 90-2148[/font]
[font=Courier New]Meier | +49 (2371) 90-3655[/font]
[font=Courier New]Schulze | +49 (2371) 90-2457[/font]
[font=Courier New]Koenig | +49 (2371) 90-3656[/font]
[font=Courier New]Ahne | +49 (2371) 90-3645[/font]
[font=Courier New]SELECT * FROM t_network;[/font]
[font=Courier New]name_network | callno_network[/font]
[font=Courier New]-------------+-------------------[/font]
[font=Courier New]Skrpek | +420 (1) 667-2927[/font]
[font=Courier New]Plsek | +420 (1) 667-2579[/font]
[font=Courier New]Mrosek | +420 (1) 667-3641[/font]
[font=Courier New]Kretschmar | +420 (1) 667-2834[/font]
[font=Courier New]Musak | +420 (1) 667-3673[/font]
Hinzufuegen einer Quellenspalte klappt:
[font=Courier New]SELECT * FROM (SELECT 'local' as source) as text NATURAL JOIN (SELECT name_local AS name, callno_local AS number FROM t_local) AS data;[/font]
[font=Courier New]source | name | number[/font]
[font=Courier New]-------+---------+--------------------[/font]
[font=Courier New]local | Mueller | +49 (2371) 90-2148[/font]
[font=Courier New]local | Meier | +49 (2371) 90-3655[/font]
[font=Courier New]local | Schulze | +49 (2371) 90-2457[/font]
[font=Courier New]local | Koenig | +49 (2371) 90-3656[/font]
[font=Courier New]local | Ahne | +49 (2371) 90-3645[/font]
Aneinanderhaengen beider Tabellen klappt:
[font=Courier New](SELECT name_local AS name, callno_local AS number FROM t_local) UNION (SELECT name_network AS name, callno_network AS number FROM t_network);[/font]
[font=Courier New]name | number[/font]
[font=Courier New]-----------+--------------------[/font]
[font=Courier New]Ahne | +49 (2371) 90-3645[/font]
[font=Courier New]Koenig | +49 (2371) 90-3656[/font]
[font=Courier New]Kretschmar | +420 (1) 667-2834[/font]
[font=Courier New]Meier | +49 (2371) 90-3655[/font]
[font=Courier New]Mrosek | +420 (1) 667-3641[/font]
[font=Courier New]Mueller | +49 (2371) 90-2148[/font]
[font=Courier New]Musak | +420 (1) 667-3673[/font]
[font=Courier New]Plsek | +420 (1) 667-2579[/font]
[font=Courier New]Schulze | +49 (2371) 90-2457[/font]
[font=Courier New]Skrpek | +420 (1) 667-2927[/font]
Die Kombination klappt zumindest so nicht:
[font=Courier New](SELECT * FROM (SELECT 'local' as source) as text NATURAL JOIN (SELECT name_local AS name, callno_local AS number FROM t_local) AS data) UNION (SELECT * FROM (SELECT 'network' as source) as text NATURAL JOIN (SELECT name_network AS name, callno_network AS number FROM t_network) AS data);[/font]
[font=Courier New]ERROR: failed to find conversion function from "unknown" to text[/font]
Die Frage lautet also: welches SQL-Kommando erzeugt die folgende Ausgabe???:
[font=Courier New]source | name | number[/font]
[font=Courier New]--------+------------+--------------------[/font]
[font=Courier New]local | Ahne | +49 (2371) 90-3645[/font]
[font=Courier New]local | Koenig | +49 (2371) 90-3656[/font]
[font=Courier New]network | Kretschmar | +420 (1) 667-2834[/font]
[font=Courier New]local | Meier | +49 (2371) 90-3655[/font]
[font=Courier New]network | Mrosek | +420 (1) 667-3641[/font]
[font=Courier New]local | Mueller | +49 (2371) 90-2148[/font]
[font=Courier New]network | Musak | +420 (1) 667-3673[/font]
[font=Courier New]network | Plsek | +420 (1) 667-2579[/font]
[font=Courier New]local | Schulze | +49 (2371) 90-2457[/font]
[font=Courier New]network | Skrpek | +420 (1) 667-2927[/font]