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.

Hello Sandis Karpovs, welcome to Stack Overflow. We don't know your data, and we can't infer it from your query. Please provide a minimum viable example made of a few representative records of sample data from all relevant tables as tabular text, along with the corresponding expected results. — GMB, May 29 '20 at 23:03
Can I add a link to database dump file? As it is a small uni project, it does not have much data in it. — predefined_, May 29 '20 at 23:13
It is far better to provide the data directly in the question (as tabular text, or `create table` and `insert` statements), so the question is self-contained. Also, building a minimum reproducible example is a first step towards solving the issue yourself, and shows the community the effort you put into improving the quality of your question. — GMB, May 29 '20 at 23:17
Does this answer your question? [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) — philipxy, May 30 '20 at 07:45
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Give up your overall goal, chop code to the 1st expression not giving what you expect & say what you expected & why. — philipxy, May 30 '20 at 07:48

score 0 · Accepted Answer · answered May 29 '20 at 23:48

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;

Thank you so much! I am relatively new into databases, and it seems that I have missed this kind of filtering conditions in WHERE clause. — predefined_, May 29 '20 at 23:54

PostgreSQL table join

1 Answers1