Recent

Author Topic: reducing rows in sql query  (Read 348 times)

lawman

  • New Member
  • *
  • Posts: 43
reducing rows in sql query
« on: December 07, 2020, 11:53:32 pm »
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

  • Sr. Member
  • ****
  • Posts: 271
Re: reducing rows in sql query
« Reply #1 on: December 08, 2020, 09:46:09 am »
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)
Best regards
paweld

 

TinyPortal © 2005-2018