从一个具有外键列的表中选择？

Question

这是我的查询：

SELECT 
    seminar.oberbegriff AS Oberbegriff, 
    seminar.beschreibung AS Beschreibung, 
    seminar.semester AS Semester,"
    seminar.titel AS Titel, dozent.nachname AS Dozent, 
    student.nachname AS Student, seminar.status AS Status
FROM
    seminar 
INNER JOIN 
    student ON seminar.titel = student.seminar_seminarThema 
INNER JOIN 
    dozent ON seminar.titel = dozent.seminar_seminarThema;

在我的表研讨会中，我有两个列（dozent_name 和student_name）是外键。我的主要键研讨会是titel，student和dozent它是nachname 。

这个说法有什么问题？

CREATE TABLE IF NOT EXISTS public.dozent
(
    akademischergrad character varying(30) COLLATE pg_catalog."default",
    vorname character varying(39) COLLATE pg_catalog."default" NOT NULL,
    nachname character varying(30) COLLATE pg_catalog."default" NOT NULL,
    passwort character varying(30) COLLATE pg_catalog."default" NOT NULL,
    email character varying(30) COLLATE pg_catalog."default" NOT NULL,
    "seminar_seminarThema" character varying(50) COLLATE pg_catalog."default",

    CONSTRAINT dozent_pkey PRIMARY KEY (nachname),
    CONSTRAINT "seminarThema" 
        FOREIGN KEY ("seminar_seminarThema")
        REFERENCES public.seminar (titel) MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION
    NOT VALID ) WITH (OIDS = FALSE ) TABLESPACE pg_default;

和学生表：

CREATE TABLE IF NOT EXISTS public.student
(
    vorname character varying(30) COLLATE pg_catalog."default" NOT NULL,
    nachname character varying(30) COLLATE pg_catalog."default" NOT NULL,
    email character varying(30) COLLATE pg_catalog."default" NOT NULL,
    passwort character varying(30) COLLATE pg_catalog."default" NOT NULL,
    matrikelnr character varying(30) COLLATE pg_catalog."default" NOT NULL,
    "belegtesSeminar" character varying(10) COLLATE pg_catalog."default" NOT NULL,
    studiengang character varying(30) COLLATE pg_catalog."default" NOT NULL,
    abschluss character varying(30) COLLATE pg_catalog."default" NOT NULL,
    "seminar_seminarThema" character varying(30) COLLATE pg_catalog."default",
    "vortrag_vortragThema" character varying(30) COLLATE pg_catalog."default",
    "ausarbeitung_ausarbeitungThema" character varying(30) COLLATE pg_catalog."default",

    CONSTRAINT student_pkey PRIMARY KEY (nachname),
    CONSTRAINT "seminarThema" 
        FOREIGN KEY ("seminar_seminarThema")
            REFERENCES public.seminar (titel) MATCH SIMPLE
                ON UPDATE NO ACTION
                ON DELETE NO ACTION
                NOT VALID,
    CONSTRAINT "vortagThema" 
        FOREIGN KEY ("vortrag_vortragThema")
            REFERENCES public.vortrag (titel) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID ) WITH (
OIDS = FALSE)TABLESPACE pg_default;

研讨会：

CREATE TABLE IF NOT EXISTS public.seminar(
"seminarID" integer NOT NULL,
oberbegriff character varying COLLATE pg_catalog."default" NOT NULL,
beschreibung character varying COLLATE pg_catalog."default" NOT NULL,
semester character varying COLLATE pg_catalog."default" NOT NULL,
titel character varying COLLATE pg_catalog."default" NOT NULL,
"dozent_Name" character varying COLLATE pg_catalog."default" NOT NULL,
"student_Name" character varying COLLATE pg_catalog."default",
status boolean NOT NULL,
CONSTRAINT seminar_pkey PRIMARY KEY (titel),
CONSTRAINT "dozentName" FOREIGN KEY ("dozent_Name")
    REFERENCES public.dozent (nachname) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID,
CONSTRAINT "studentName" FOREIGN KEY ("student_Name")
    REFERENCES public.student (nachname) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID)WITH (    OIDS = FALSE)TABLESPACE pg_default;

Answer 1

您应该在条件上使用中的外键。

SELECT se.oberbegriff AS Oberbegriff, se.beschreibung AS Beschreibung, se.semester AS Semester
       se.titel AS Titel, d.nachname AS Dozent, st.nachname AS Student, se.status AS Status
FROM seminar AS se
INNER JOIN student AS st ON st.nachname = se.student_name
INNER JOIN dozent AS d ON d.nachname = se.dozent_name

使用表别名使您的列参考较少详细。