SELECT from one table with foreign key columns?-CodePudding

This is my query:

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;

In my table seminar, I have two columns (dozent_Name and student_Name) which are foreign keys. My primary key in seminar is titel, in student and dozent it is nachname.

What is wrong in this statement?

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;

And the student table:

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;

seminar:

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;

CodePudding user response：

You should use the foreign keys in the ON conditions.

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

Use table aliases to make your column references less verbose.