Abfrage von Daten bei Übereinstimmung

Hallo liebes Forum,

ich bin gerade dabei ein Studienportal zu entwickeln und benötige daher folgende Abfrage: selektiere alle Einträge der Tabelle Benutzer wo der Benutzer ungleich 1 ist (als Beispiel) und die Studiengänge von Benutzer 1 mit den anderen Benutzern übereinstimmen. Grundsätzlich löst folgende Query mein Problem:

SELECT u.* FROM tbl_user_study us 
	JOIN tbl_study s ON s.id = us.fk_study
	JOIN tbl_user u ON u.id = us.fk_user
		WHERE fk_study IN (SELECT fk_study FROM tbl_user_study us WHERE us.fk_user = 1) AND us.fk_user != 1;

Nur glaube ich, das dies eine nicht gerade effiziente Art und Weise ist, wie man dies abfrägt. Hat jemand vielleicht einen Verbesserungsvorschlag?

Mit freundlichen Grüßen
InformaTiger

Vielleicht könntest Du uns noch die CREATE TABLE - Befehle geben, damit wir Deine Blackbox besser & einfacher verstehen können. Vielleicht sogar mit einigen Demo-Daten und was bei rauskommen soll.

akretschmar hat recht, es ist immer besser, wenn man gleich was zum besseren verstehen und testen hat.

Ich habe es trotzdem mal versucht. Konnte es natürlich nicht prüfen:

select distinct u.*
    from tbl_user_study us1
        join tbl_user_study us2 on (us2.fk_user != us1.fk_user and us2.fk_study = us1.fk_study)
        join tbl_user u on (u.id = u2.fk_user)
    where us1.fk_user = 1;

wenn man mehrere solcher Abfragen hat, die letztendlich dasselbe liefern, ist es dann immer eine gute Idee, sich via Explain die Abfragepläne anzuschauen.

Das gilt übrigens auch, wenn man nur eine Lösung hat :wink:

Danke zunächst mal für die vielen Antworten. :slight_smile:

Entschuldigung, ich habe das in meinem Eifer impliziert. Folgt natürlich sofort:

CREATE TABLE tbl_user (
    id SERIAL PRIMARY KEY,
    name VARCHAR(16),
    surname VARCHAR(16),
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255),
    salt VARCHAR(48)
);

CREATE TABLE tbl_study (
    id SERIAL PRIMARY KEY,
    name VARCHAR(48) UNIQUE
);

CREATE TABLE tbl_user_study (
    id SERIAL UNIQUE,
    fk_user INT REFERENCES tbl_user(id),
    fk_study INT REFERENCES tbl_study(id),
    PRIMARY KEY(fk_user, fk_study)
);

CREATE TABLE tbl_lecture (
    id SERIAL PRIMARY KEY,
    name VARCHAR(32) UNIQUE
);

CREATE TABLE tbl_study_lecture (
    id SERIAL UNIQUE,
    fk_study INT REFERENCES tbl_study(id),
    fk_lecture INT REFERENCES tbl_lecture(id),
    PRIMARY KEY(fk_study, fk_lecture)
);
CREATE TABLE tbl_card (
    id SERIAL PRIMARY KEY,
    title VARCHAR(32),
    fk_lecture INT REFERENCES tbl_lecture(id),
    fk_owner INT REFERENCES tbl_user(id),
    time_modified TIMESTAMP,
    time_created TIMESTAMP,
    content TEXT
);

CREATE TABLE tbl_card_share(
    id SERIAL UNIQUE,
    fk_card INT REFERENCES tbl_card(id),
    fk_receiver INT REFERENCES tbl_user(id),
    can_edit BOOLEAN,
    PRIMARY KEY(fk_card, fk_receiver)
);

Das ist meine Datenbank. Was mir allerdings bei der Aufgabenstellung noch aufgefallen ist, ist dass der Benutzer nicht nur generell denselben Studiengang haben sollte, sondern auch diesselbe Vorlesung besuchen muss. Im großen Ganzen geht es nämlich derzeit darum, dass eine Karte für einen Benutzer freigegeben werden kann und zwar eben nur für Benutzer die denselben Studiengang und dieselbe Vorlesung besuchen, wie der Benutzer der die Karte freigeben möchte.

Also beispielhaft wenn die Datenbank den Herrn und die Frau Mustermann beinhaltet und die beiden unterschiedliche Studiengänge beziehungsweise unterschiedliche Vorlesungen besuchen soll es den beiden nicht möglich sein Karten untereinander zu teilen. Ob ein Benutzer mit mir in der gleichen Vorlesung und Studiengang sitzt möchte ich herausfinden.

Ich hoffe das ist soweit verständlich. Danke schon mal inzwischen.

Mit freundlichen Grüßen
InformaTiger

Hallo informaTiger,

ich hoffe dieser Select liefert das gewünschte Ergebnis. Konnte es bisher nur auf Syntax testen und nicht inhaltlich, da die Tabellen noch leer bei mir sind. :sunglasses:

Es wird als Parameter nur die Card Id benötigt. Der Select sollte die User ermitteln, die in der selben Vorlesung waren.

select u.*
    from tbl_card c
        join tbl_study_lecture sl on (sl.fk_lecture = c.fk_lecture)
        join tbl_user_study us on (us.fk_study = sl.fk_study)
        join tbl_user u on (u.id = us.fk_user)
    where c.id = 1
        and u.id != c.fk_owner
;

Allerdings gibt es keine Tabelle die beschreibt, welcher user in welcher lecture war. Sondern nur zur welcher Study er gehört. Damit ist der User automatisch in allen lecture der study. Das ist so gewollt?

Gruß frastr

Hi,

@frastr zeigte ja schon eine Lösung.

Aber vielleicht noch zum Tabellenaufbau:

  • varchar(N) mach mittelfristig mehr Probleme als es Nutzen bringt. Irgendwann wirst Du einen Benutzernamen vielleicht nicht mehr in varchar(16) unterbringen etc. TEXT ist deutlich flexibler.
  • die id-spalte in tbl_user_study ist nutzlos.
  • in tbl_card könnte der PK auch auf die 2 FK-Felder gelegt werden, wenn ich das richtig sehe. Dann wäre auch da id überflüssig, und Du verhindert multiple Einträge. Oder kann / soll das zulässing sein?
  • time_created könnte gleich einen passenden Default-Wert bekommen
  • Indexe?

Was passiert, wenn Benutzer ausscheiden?