Suche nach Tabelleneintrag mit Zweit-Tabelle

[Papp Nase]

Hallo,

ich habe eine Datenbank namens "tiere" mit zwei Tabellen

haustiere (ID, Tier, Name, Besitzer)
besitzer (ID, Name, Wohnort, Telefon)

Folgende Beziehung: haustier(Besitzer) = besitzer(Name)

Ich möchte alle Tiere (komplette Zeile) finden, die in einem Wohnort vorkommen:

Folgender Ansatz

SELECT * FROM haustiere WHERE Name = "XYZ"

Das funktioniert gut.

Sortierung nach Namen, die in der gewünschten Stadt wohnen:

SELECT Name FROM besitzer WHERE Wohnort = "XYZ"

klappt auch wunderbar.

Nun die Kopplung:

SELECT * FROM haustiere WHERE Name = (SELECT Name FROM besitzer WHERE Wohnort = "XYZ")

Das klappt nicht. Fehlermeldung: "#1242 - Subquery returns more than 1 row"

Ist mein Ansatz mit den geschachtelten Abfragen so vom Ansatz richtig, nur dass vielleicht ein einfaches Schlüsselwort noch fehlt, damit Primärabfrage mehrere Einträge akzeptiert, oder ist die Vorgehensweise ganz anders? Die Sekundärabfrage müsste irgendwie mit einem OR an den Namen gekoppelt werden. Geht das?

 

[Hony%]

Geht das?

Nein. Das mit dem Subselect und WHERE funktioniert bei einem solchen Ausdruck nur dann wenn ein singulärer Wert zurück gegeben wird.

Was du in diesem Fall suchst ist ein JOIN:

SELECT T.*
FROM haustiere AS T
INNER JOIN besitzer AS B
ON T.besitzer = B.name
WHERE B.wohnort = 'XYZ'

Mit Subselect geht es dann allerdings doch wenn du den Ausdruck änderst:

SELECT *
FROM haustiere
WHERE besitzer IN (
SELECT name
FROM besitzer
WHERE wohnort = 'XYZ'
)

Die Zugriffskosten sind für den Join allerdings geringer. Darüber musst du dir beim lernen noch keine Gedanken machen. Das spielt erst bei großen Datenmengen eine Rolle.

 

[akretschmer]

Hallo,

ich habe eine Datenbank namens "tiere" mit zwei Tabellen

haustiere (ID, Tier, Name, Besitzer)
besitzer (ID, Name, Wohnort, Telefon)

Folgende Beziehung: haustier(Besitzer) = besitzer(Name)

Meep!

Du hast eine ID in Besitzer, das ist sicherlich auch der primary Key. Die Verbindung in Haustiere zu besitzer sollte nun als Foreign Key in Haustiere auf die ID in Besitzer sein.

Etwa so:

test=# create table besitzer(id int primary key, name text);
CREATE TABLE
test=*# create table tiere (id int primary key, besitzer int references besitzer, name text);
CREATE TABLE

 

[Papp Nase]

Danke fuer die Antwort.

Mit Subselect geht es dann allerdings doch wenn du den Ausdruck änderst:

SELECT *
FROM haustiere
WHERE besitzer IN (
SELECT name
FROM besitzer
WHERE wohnort = 'XYZ'
)

Dass hat funktioniert.
Ich habe noch das Schlüsselwort ANY gefunden, dass geht auch:

 SELECT * FROM haustiere WHERE Besitzer = ANY (SELECT Name FROM besitzer WHERE Wohnort = "XYZ")

Folgendes Problem hab ich beim Nachdenken festgestellt. Es könnte ja sein, dass es einen Namen doppelt gibt, aber mit zwei unterschiedlichen Adressen bei den Besitzern. Meinst Du damit:

Meep!
Du hast eine ID in Besitzer, das ist sicherlich auch der primary Key. Die Verbindung in Haustiere zu besitzer sollte nun als Foreign Key in Haustiere auf die ID in Besitzer sein.

dass ich bei haustiere.besitzer nicht den dierekten Namen reinschreibe z.B. Frank oder Otto, sondern den PrimKey, den es nur einmal gibt und damit bei der Tabelle besitzer dann dem Namen zugeordnet werden kann?

 

[Papp Nase]

Ich hab zum Spaß noch eine dritte Tabelle erstellt und mit Werten gefüllt:

wildtiere (ID, Tier, Name, Besitzer)

Ich habe jetzt versucht, in beiden Tiertabellen gleichzeitig nach Besitzern zu suchen:

SELECT * FROM (wildtiere AND haustiere) WHERE Besitzer = "Otto"

.

Das klappte leider nicht.

Ich habe eine Frage - angenommen, ich mache eine Suche und möchte Ergebnisse zwischenspeichern. Es könnte ja sein, dass ich z.B. wissen möchte, wie viele Wildtiere und wie viele Haustiere ein Besitzer hat. In einer Programmiersprache würde ich mir eine kleine Variable erstellen, um ein Ergebnis zwischenzuspeichern. Könnte ich mir einfach eine kleine neue Tabelle erstellen, um Ergebnisse zwischenzuspeichern?

Oben wurde ein Beispiel mit Join angeführt:
SELECT T.* ... AS T

Ist jetzt T. schon gewissermaßen eine Variable?

Und verkürze ich mir damit die Datenbankzugriffe (und dadurch entstehende Traffickosten), weil ich mir die Ergebnisse so in den Speicher lade und weitere Bearbeitungen dann mit verkürzten Tabellen bzw. Tabellenausschnitten im Arbeitsspeicher erfolgen?

 

[Hony%]

dass ich bei haustiere.besitzer nicht den dierekten Namen reinschreibe z.B. Frank oder Otto, sondern den PrimKey, den es nur einmal gibt und damit bei der Tabelle besitzer dann dem Namen zugeordnet werden kann?

Genau das meint @akretschmer damit. In diesem Fall führt dann auch kein Weg am JOIN vorbei.

Ich habe jetzt versucht, in beiden Tiertabellen gleichzeitig nach Besitzern zu suchen

In diesem Fall erzeugst du erst eine neue Menge aus haustiere und wildtiere. Wichtig ist dabei, dass die beiden Relationen die gleiche Anzahl Attribute hat. Anschließend kannst du die neue Menge wie eine Tabelle benutzen:

SELECT *
FROM (
    SELECT * FROM haustiere
    UNION
    SELECT * FROM wildtiere
) AS T
WHERE Besitzer = "Otto";

Ich habe eine Frage - angenommen, ich mache eine Suche und möchte Ergebnisse zwischenspeichern. Es könnte ja sein, dass ich z.B. wissen möchte, wie viele Wildtiere und wie viele Haustiere ein Besitzer hat. In einer Programmiersprache würde ich mir eine kleine Variable erstellen, um ein Ergebnis zwischenzuspeichern. Könnte ich mir einfach eine kleine neue Tabelle erstellen, um Ergebnisse zwischenzuspeichern?

Das kannst du mit einer View erreichen:

CREATE VIEW Name_des_View AS
SELECT * FROM haustiere;

Ist jetzt T. schon gewissermaßen eine Variable?

T ist ein Alias, also ein Name, für die erzeugte Menge. Traffickosten sollten möglichst nie entstehen wenn du mit einer Datenbank arbeitest. Bei einer lokalen Datenbank ist das auch nicht der Fall.

Mit Hilfe von Views kannst du komplizierte Abfragen vorbereiten und dann wie eine Tabelle benutzen. Da eine VIEW immer neu erzeugt wird reduziert das nicht die Zugriffskosten und auch nicht den Speicherbedarf. Das können nur Materialized Views leisten. Dazu sind aber tiefgreifende Kenntnisse notwendig. Außerdem wird das auch erst bei richtig großen Datenbanken notwendig.

 

[Papp Nase]

Vielen Dank für Eure Antworten.

... Außerdem wird das auch erst bei richtig großen Datenbanken notwendig.

Also meine Datenbank muss schon sehr groß sein. Bei den Wildtieren habe ich bereits 3 Elefanten drinnen und einen Löwe

Was ist denn eigentlich eine "große" Datenbank? Wären 1000 Kundendaten in einer Firma schon groß?

 

[Hony%]

Wären 1000 Kundendaten in einer Firma schon groß?

Nicht einmal annähernd. Häng da mal noch so 3+ Nullen dran in 20+ Tabellen. Mach dir da am besten keine Gedanken. Optimiert wird erst wenn ein Flaschenhals spürbar wird. Alles Andere ist Zeitverschwendung.

 

[Papp Nase]

Vielen Dank für Eure Antworten.