UNION mit zusätzlicher Quellen-Spalte

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 :frowning:

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]

select ‘tabelle a’ as quelle, * from a union all select ‘tabelle b’, * from b;

Andreas

Immerhin wußte ich, dass es ganz einfach sein müßte … Danke!

Die endgültige Lösung (wohl im wesentlichen für mich selbst) ist also:

SELECT 'local'   AS source, name_local   AS name, callno_local   AS number FROM t_local
UNION SELECT 'network' AS source, name_network AS name, callno_network AS number FROM t_network;
 
[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]local   | Meier      | +49 (2371) 90-3655[/font]
[font=Courier New]local   | Mueller    | +49 (2371) 90-2148[/font]
[font=Courier New]local   | Schulze    | +49 (2371) 90-2457[/font]
[font=Courier New]network | Kretschmar | +420 (1) 667-2834[/font]
[font=Courier New]network | Mrosek     | +420 (1) 667-3641[/font]
[font=Courier New]network | Musak      | +420 (1) 667-3673[/font]
[font=Courier New]network | Plsek      | +420 (1) 667-2579[/font]
[font=Courier New]network | Skrpek     | +420 (1) 667-2927[/font]

Ich hoffe, das waren jetzt keine realen Namen und Telefonnummern …

Wir waren ja nicht dabei…

Andreas