Subquery zu Ergebnis hinzufügen

[Keeto]

Hallo!

Ist es möglich, die Ergebnisse einer Subquery mit in die Ergebnisse der gesamten Abfrage reinzunehmen? So wie ein UNION über 2 Abfragen?

Hintergrund ist folgender:
Ich habe eine Query, die zwar macht, was sie soll, aber sie ist wahrscheinlich sehr unnötig umständlich geschrieben und liest Daten doppelt aus.

Die Query sieht folgendermaßen aus:

SELECT p.name FROM place_transitions AS pt
INNER JOIN places AS p ON p.id = pt.pid_to
WHERE pt.pid_from = *num*
UNION SELECT pa.alt from place_alternatives AS pa
WHERE pa.pid IN
(SELECT p.id FROM place_transitions AS pt
INNER JOIN places AS p ON p.id = pt.pid_to
WHERE pt.pid_from = *num*)

Das fett gedruckte steht ja blöderweise zweimal da. Ich bin mir sicher, dass es eine einfachere und schnellere Lösung gibt.
Kann hier jemand was zu sagen?

Vielen Dank und
freundliche Grüße
Christoph

 

[akretschmer]

Ich bin mir sicher, dass es eine einfachere und schnellere Lösung gibt.
Kann hier jemand was zu sagen?

Zeig Deine Tabellen, ein paar Beipsiel-Datensätze, und was rauskommen soll.

 

[Keeto]

Man kommt wohl an die Ergebnisse der Subquery nicht so einfach ran?

Dann hol ich mal weiter aus:
Es geht um einen Parser für ein Textadventure.
Dieser soll in einem Text Begriffe hervorheben.
Ausgehend von der Raum-ID, in der sich der Spieler befindet, soll aus 'place_transitions' ausgelesen werden, wohin der Spieler gehen kann.
Dort stehen allerdings nur die IDs drin, weshalb ich den Namen aus 'places' mit auslese und erhalte somit eine Liste von Orten, die vom aktuellen Ort aus erreichbar sind (=hervorzuhebende Begriffe).

Tabelle places
id (bigint), name (varchar), text (varchar)
Beispiele:
1 - Haus - Dies ist dein Haus.
2 - Straße - Die Straße vor deinem Haus.
3 - Bushaltestelle - Der Bus fährt zum Markt.

Tabelle place_alternatives (beinhaltet alternative Schreibweisen für Eintrag aus 'places')
id (bigint), pid (bigint), alt (varchar)
Beispiele:
1 - 1 - Wohnhaus
2 - 3 - Bus
3 - 3 - Haltestelle
4 - 3 - Markt

Tabelle place_transitions (hier stehen die Übergänge drin, also von wo aus man wo hin kommt)
id (bigint), pid_from (bigint), pid_to (bigint)
Beispiele:
1 - 2 - 1 /* Man kommt von der Straße zum Haus */
2 - 2 - 3 /* Man kommt von der Straße zur Bushaltestelle */
3 - 3 - 2 /* Man kommt von der Bushaltestelle zur Straße */
4 - 1 - 2 /* Man kommt vom Haus zur Straße */

Was ich nun möchte ist, dass in diesem Ergebnis (also der Liste aller Orte, zu denen man gehen kann) auch die alternativen Schreibweisen der jeweiligen Orte enthalten sind.
Also am obigen Beispiel:
Spieler befindet sich auf der Straße (places.id = 2)
Er kann zum Haus und zur Bushaltestelle gehen.
Das Haus und die Bushaltestelle haben aber auch alternative "Schreibweisen".
Für die übergebene ID 2 möchte ich also nun folgende Liste bekommen:
Haus, Wohnhaus, Bushaltestelle, Bus, Haltestelle, Markt

 

[akretschmer]

Was ich nun möchte ist, dass in diesem Ergebnis (also der Liste aller Orte, zu denen man gehen kann) auch die alternativen Schreibweisen der jeweiligen Orte enthalten sind.
Also am obigen Beispiel:
Spieler befindet sich auf der Straße (places.id = 2)
Er kann zum Haus und zur Bushaltestelle gehen.
Das Haus und die Bushaltestelle haben aber auch alternative "Schreibweisen".
Für die übergebene ID 2 möchte ich also nun folgende Liste bekommen:
Haus, Wohnhaus, Bushaltestelle, Bus, Haltestelle, Markt

test=*# select * from places;
 id |  name  |  info
----+----------------+----------------------------
  1 | haus  | dies ist dein haus
  2 | straße  | die straße vor deinem haus
  3 | bushaltestelle | der bus fährt zum markt
(3 rows)

Time: 0,153 ms
test=*# select * from places_alternatives ;
 id | pid |  alt
----+-----+-------------
  1 |  1 | wohnhaus
  2 |  3 | bus
  3 |  3 | haltestelle
  4 |  3 | markt
(4 rows)

Time: 0,156 ms
test=*# select * from place_transitions ;
 id | pid_from | pid_to
----+----------+--------
  1 |  2 |  1
  2 |  2 |  3
  3 |  3 |  2
  4 |  1 |  2
(4 rows)

Time: 0,147 ms
test=*# select array_to_string(array_agg(ziel),',') from (select name || ',' || array_to_string(array_agg(alt),',') as ziel from(select * from place_transitions pt left join places p on pt.pid_to=p.id left join places_alternatives pa on pt.pid_to=pa.pid  where pid_from = 2) bla group by name) ziel;
  array_to_string
----------------------------------------------------
 haus,wohnhaus,bushaltestelle,bus,haltestelle,markt
(1 row)

 

[akretschmer]

noch etwas hübscher:

test=*# select array_to_string(array_agg(ziel),' oder ') from (select (row_number() over ())::text || ': ' || name || ',' || array_to_string(array_agg(alt),',') as ziel from (select * from place_transitions pt left join places p on pt.pid_to=p.id left join places_alternatives pa on pt.pid_to=pa.pid  where pid_from = 2) bla group by name) ziel;
  array_to_string
---------------------------------------------------------------
 1: haus,wohnhaus oder 2: bushaltestelle,bus,haltestelle,markt
(1 row)

 

[Keeto]

Ahja, Postgre.. Jetzt seh ich es auch in deiner Signatur..
Ich hätte vielleicht erwähnen sollen, dass es um MySQL geht.
Vielen Dank für die Mühen, aber damit kann ich so leider nichts anfangen (und verstehen tu ich es leider auch nicht ).

Also vielleicht nochmal anders:
Gibt es in MySQL die Möglichkeit, die Ergebnisse aus Subqueries den Ergebnissen der gesamten Abfrage hinzuzufügen a la "UNION"?
Falls ja, wie geht das?
Falls nein, wie könnte man bei der Abfrage aus dem ersten Post stattdessen die Redundanz vermeiden?

 

[akretschmer]

Ahja, Postgre.. Jetzt seh ich es auch in deiner Signatur..

Ja.

Also vielleicht nochmal anders:
Gibt es in MySQL die Möglichkeit, die Ergebnisse aus Subqueries den Ergebnissen der gesamten Abfrage hinzuzufügen a la "UNION"?
Falls ja, wie geht das?

Das könnte in etwas auch in MySQL gehen. Das hat übrigens nix mit UNION zu zun.

 

[Distrilec]

@akretschmer Man kann ab und an auch mal im SQL-Standard bleiben

In etwa so sollte es gehen:
(Davon ausgehend das ich dein ID-Chaos richtig verstanden habe...)

Select p.name
From   place_transitions pt
Inner  Join places As p
On     p.id = pt.pid_to
Where  pt.pid_from = *num*

Union

Select pa.alt
From   place_alternatives pa
Inner  Join place_transitions pt
On     pt.pid_to = pa.pid
And    pt.pid_from = *num*

 

[akretschmer]

@akretschmer Man kann ab und an auch mal im SQL-Standard bleiben

In etwa so sollte es gehen:

Damit bekommt er es aber nicht in dieser von Ihm gewünschten Reihenfolge und Form ;-)

 

[Distrilec]

Ja, ist ja gut... Aber genauer geht dann wirklich nicht...

Select t.name
From   (Select p.id as main_id,
       '1' as pseudo_id
       p.name as name
From   place_transitions pt
Inner  Join places As p
On     p.id = pt.pid_to
Where  pt.pid_from = *num*

Union

Select pa.pid,
       '2'
       pa.alt
From   place_alternatives pa
Inner  Join place_transitions pt
On     pt.pid_to = pa.pid
And    pt.pid_from = *num*) t
Order  By t.main_id, t.pseudo_id

 

[Keeto]

Vielen Dank für eure Antworten - genau nach so einer einfachen Lösung hab ich gesucht! Da stand ich ja wirklich sehr auf'm Schlauch..
Die Reihenfolge der Ergebnisse ist tatsächlich in diesem Fall nicht von Bedeutung, weshalb die erste Query von Distrilec schon die Lösung darstellt. Das ID Chaos hast du offenbar gut entschlüsselt ;-)
Schöne Woche und
beste Grüße!
Christoph