PostgreSQL table join

Question

I have a lecture attendance database as an uni project. As one of views, i came up with idea that I could make missed_lectures view with all records of Not attended or Sick attendance types. When I am making this query, it returns 2,5k rows, which is not correct.

Query looks like this

CREATE OR REPLACE VIEW missed_lectures AS
SELECT CONCAT(s.student_name, ' ', s.student_surname) AS "Student", sc.course_title AS "Study course", atp.attendance_type AS "Attendance type", a.record_date AS "Date"
FROM students AS s, study_courses AS sc, attendance_type AS atp, attendance AS a
WHERE
s.student_id=a.student_id AND
sc.course_id=a.course_id AND
a.attendance_type_id=atp.attendance_type_id AND
a.attendance_type_id=(SELECT attendance_type_id FROM attendance_type WHERE attendacne_type='Sick') OR
a.attendance_type_id=(SELECT attendance_type_id FROM attendance_type WHERE attendance_type='Not attended')
GROUP BY s.student_name, s.student_surname, sc.course_title, atp.attendance_type, a.record_date;

This is the last query I came up with, but as I mentioned earlier, it returns 2,5k rows with incorrect data. Can anybody spot the issue here?

EDIT: table students is

CREATE TABLE students(
student_id serial PRIMARY KEY NOT NULL,
student_name VARCHAR(30) NOT NULL,
student_surname VARCHAR(35) NOT NULL,
matriculation_number VARCHAR(7) NOT NULL CHECK(matriculation_number ~ '[A-Z]{2}[0-9]{5}'),
faculty_id INT NOT NULL,
course INT NOT NULL,
phone_number CHAR(8) CHECK(phone_number ~ '^2{1}[0-9]{7}'),
email VARCHAR(35),
gender VARCHAR(10)
);

sample data:

INSERT INTO students (student_name, student_surname, matriculation_number, faculty_id, course, phone_number, email, gender)
VALUES
('Sandis','Bērziņš','IT19047',7,1,'25404213','sandis.berzins@gmail.com','man'),
('Einārs','Kļaviņš','IT19045',7,1,'24354654','einars.klavins@gmail.com','man'),
('Jana','Lapa','EF18034',8,2,'26224941','lapajana@inbox.lv','woman'),
('Sanija','Bērza','EF18034',8,2,'24543433','berzasanija@inbox.lv','woman'),
('Valdis','Sijāts','TF19034',4,1,'25456545','valdis.sijats@gmail.com','man'),
('Jānis','Bānis','IT17034',7,3,'24658595','banis.janis@inbox.lv','man');

table study_courses is

CREATE TABLE study_courses(
course_id serial PRIMARY KEY NOT NULL,
course_title VARCHAR(55) NOT NULL,
course_code VARCHAR(8) NOT NULL CHECK(course_code ~ '[a-zA-Z]{4}[0-9]{4}'),
credit_points INT
);

sample data:

INSERT INTO study_courses (course_title, course_code, credit_points)
VALUES
('Fundamentals of Law','JurZ2005',2),
('Database technologies II','DatZ2005',2),
('Product processing','PārZ3049',4),
('Arhitecture','Arhi3063',3),
('Forest soils','LauZ1015',4);

Table attendance_type is:

CREATE TABLE attendance_type(
attendance_type_id serial PRIMARY KEY NOT NULL,
attendance_type VARCHAR(15) NOT NULL
);

sample data:

INSERT INTO attendance_type (attendance_type)
VALUES
('Attended'),
('Not attended'),
('Late'),
('Sick');

table attendance is:

CREATE TABLE attendance(
record_id serial PRIMARY KEY NOT NULL,
student_id INT NOT NULL,
course_id INT NOT NULL,
attendance_type_id INT NOT NULL,
lecturer_id INT,
lecture_type_id INT NOT NULL,
audience_id INT NOT NULL,
record_date DATE NOT NULL
);

sample data:

INSERT INTO attendance (student_id, course_id, attendance_type_id, lecturer_id, lecture_type_id, audience_id, record_date)
VALUES
(1,2,1,1,1,14,'20-05-2020'),
(2,2,1,1,1,14,'20-05-2020'),
(6,9,1,13,2,2,'20-05-2020'),
(22,9,2,13,2,2,'20-05-2020'),
(24,9,3,13,2,2,'20-05-2020');

Hoping this will help.

user_42346 · Answer

The problem is that OR condition in your WHERE clause.
You have to surround it by parentheses if you're going to do it that way.
However, you're already querying the attendance_type table so you can just use it to filter for those two attendance type conditions.

SELECT 
    CONCAT(s.student_name, ' ', s.student_surname) AS "Student", 
    sc.course_title AS "Study course", 
    atp.attendance_type AS "Attendance type", 
    a.record_date AS "Date"
FROM 
    students AS s, study_courses AS sc, 
    attendance_type AS atp, attendance AS a
WHERE
    s.student_id=a.student_id AND
    sc.course_id=a.course_id AND
    a.attendance_type_id=atp.attendance_type_id AND
    -- filter for these two conditions without subqueries
    atp.attendance_type in ('Sick','Not attended') 
GROUP BY 
    CONCAT(s.student_name, ' ', s.student_surname), sc.course_title, 
    atp.attendance_type, a.record_date;

PostgreSQL table join

1 Answers