SELECT from one table with foreign key columns?

Question

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;

I don't understand your tables. There is one table called `student`. So I expect it to contain the students (one student per row in the table). But then you have `student.seminar_seminarThema`. If the seminar is in the student table and there is one row per student, then this means that each student attends only one seminar. Is this really the case? Same for `dozent`. Only one seminar per dozent? This doesn't sound likely. It seems your database is flwed and you should have an additional table to establish an m:n relation (student_seminar). — Thorsten Kettner, Jul 07 '22 at 19:15
Nachname (surname) as a primary key? There cannot be two students with the same last name? Weird. — Thorsten Kettner, Jul 07 '22 at 19:17
Oh, and `dozent_Name` and `studentName` in the table `seminar`? Okay, only one dozent per seminar. This makes sense. But only one student in a seminar? Wow, that's exclusive. — Thorsten Kettner, Jul 07 '22 at 19:21
you are right. I should change primary keys to the IDs. And yes, each student/dozent is supposed to attend to only one seminar. Thank you — , Jul 07 '22 at 19:29
Oh, that is unexpected (and I had to delete my answer :-) And is it on purpose that only one student can attend a seminar? — Thorsten Kettner, Jul 07 '22 at 19:39
Yes, thats on purpose. If i may ask you another question: If i also want to display rows, in which there is seminars, but no students assigned to (means NULL value), i put in a INNER JOIN student ON student.seminarseminarthema = seminar.titel OR student.seminarseminarthema IS DISTINCT FROM seminar.titel is that correct? If seminarseminarthema isn't equal to seminar.titel, it should be displayed, right? — , Jul 07 '22 at 19:57
INNER JOIN student ON student.seminarseminarthema = seminar.titel OR seminar.studentname IS NULL It just doesn't display the rows in which studentname is null — , Jul 07 '22 at 20:00
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246253/discussion-between-thorsten-kettner-and-der-papa). — Thorsten Kettner, Jul 07 '22 at 20:07

Barmar · Accepted Answer · 2022-07-07T19:08:18.653

0

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.

edited Jul 07 '22 at 19:08

answered Jul 07 '22 at 18:59

Barmar

741,623
53
500
612

I did that, but it still doesn't work. SELECT oberbegriff AS Oberbegriff, beschreibung AS Beschreibung, semester AS Semester, titel AS Titel, dozent_Name AS Dozent, student_Name AS Student, status AS Status FROM seminar INNER JOIN student ON student.seminar_seminarThema = seminar.titel INNER JOIN dozent ON dozent.seminar_seminarThema = seminar.titel; but my error code is also weird. it says: ERROR: column student.seminar_seminarthema does not exist Hinweis: Perhaps you meant to reference the column "student.seminar_seminarThema". even though i spelled it correctly – Jul 07 '22 at 19:05
Please add your table definitions to the question. `SHOW CREATE TABLE seminar`, `SHOW CREATE TABLE student` and `SHOW CREATE TABLE dozent` – Barmar Jul 07 '22 at 19:07
I did, i pasted them – Jul 07 '22 at 19:14
1

I guess your database is case-sensitive in column names. – Barmar Jul 07 '22 at 19:17
See [here](https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive) regarding case-sensitivity of PostGres column names. – Barmar Jul 07 '22 at 19:18
it was the case-sensitivity. thank you very much – Jul 07 '22 at 19:30

SELECT from one table with foreign key columns?

1 Answers1