sql - Join 4 tables in PostgreSQL

Question

I have four tables and the structures are:

//Table Person
SSN (PK) | name
123456789 | AA
234567890 | BB

//Table Teacher
teacher_id (PK) |  SSN
1 | 123456789
2 | 234567890

//Table Lesson_a
lesson_id (PK) | teacher_id
101 | 1
102 | 1
103 | 2

//Table Lesson_b
lesson_id (PK) | teacher_id
111 | 1
112 | 1
113 | 2

And I got this so far, after I joined the first 3 tables:

person.name | teacher_id | number of lessons
AA | 1 | 2
BB | 2 | 1

But I want to get:

person.name | teacher_id | number of lessons
AA | 1 | 4
BB | 2 | 2

And I've gotten this far:

SELECT 
person.name, 
person.name,
COUNT(teacher.teacher_id) AS "No of lessons" 
FROM teacher
INNER JOIN lesson_a
ON teacher.teacher_id = lesson_a.teacher_id
INNER JOIN person 
ON person.ssn = teacher.ssn
GROUP BY teacher.teacher_id, lesson.teacher_id
ORDER BY teacherr.teacher_id ;

How should I join the last table lesson_b at the right way?

Answer 1

You can use union all before you aggregate:

select p.name, count(*)
from person p join
     teacher t
     on p.ssn = t.ssn join
     (select teacher_id from lesson_a union all
      select teacher_id from lesson_b
     ) l
     on l.teacher_id = t.teacher_id
group by p.name, p.ssn;

Some comments on the data model. Splitting the lessons into two tables seems like a very bad choice for the data model. There should be one table for lessons with a column specifying "a" or "b".

Perhaps worse is using ssn as a key in the person table. SSNs are personally identifiable information (PII) and should be protected when stored in databases -- even if the laws in your particular jurisdiction don't actually require such protection.