reducing rows in sql query
lazarus / 16-Databases

----------------------------------------------------------------------
lawman · 2020-12-07 22:53

I have below query, with 3 tables
1.  Student
2.  Class
3.  StudentClass-Join


SELECT Student.Surname, Class.Room
FROM [Class]
INNER JOIN (Student
INNER JOIN [StudentClass-Join]
ON Student.[ID] = [StudentClass-Join].[StudentID])
ON Class.[ID] = [StudentClass-Join].[ClassID];


StudentClass-Join lists all the students in each class, using foreign
keys to the primary keys of tables Student, Class.

If I have 3 students AA,BB,CC in Class 11 I get 3 rows showing each
student name next to class.

ie.
Student - Class
AA, 11
BB, 11
CC, 11

** Where I have multiple students in a class, I would just like it to
show a single row of the first students name with '+' sign next to it.

ie.
Student - Class
AA+, 11

** If possible, it would also be good to have a number returned and
stored in a field in Class table.  eg. Class.NumberStudents = 3

How can I achieve this in :

1.  MS-Access
2.  Firebird
3.  Sqlite


I ask for all three as I'm learning all 3.    I'm hoping the sql code
will be same.

Short term, MS-Access for immediate implementation without a program.

Longer term I've trying create a program in lazarus (free/easier to
learn) to use a database such as firebird or sqlite with better
implementation

Thanks in advance for any guidance.


ps.   The same schema/data in access, firebird, sqlite results in
about 2.5M, 3.5M and 300kb.      Is Sqlite always smaller almost 1/10
of firebird database size?

----------------------------------------------------------------------
paweld · 2020-12-08 08:46

for sqlite like this:
Code: [Select]select t.students, c.name class, t.students_count
from (select sc.idc, min(s.name) || case when count(*)>1 then ' +'
else '' end students, count(*) students_count from student s inner
join studentclassjoin sc on (s.id=sc.ids) group by sc.idc) t
inner join class c on (t.idc=c.id)