Again this query

[newbie2]

Hello VbForums
Last time I had a similar post and I'm so grateful to the experts of VbForums who helped me.
(Zvoni , techgnome, Olaf and others)
I apologize because I was not clear enough, and I did not define my needs clearly.

In fact I have a Class_tbl which holds all the pupils of a class.
And an Another child table (Absence_tbl) that holds the absentees.

Set Cnn = New_c.Connection(, DBCreateInMemory)
With Cnn.NewFieldDefs
 .Add "ID   Integer Primary key"
 End With
Cnn.CreateTable "Class_tbl"

    Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('1')"
    Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('2')"
    Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('3')"
    Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('4')"
    Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('5')"
    Cnn.Execute "INSERT INTO Class_tbl (ID) VALUES ('6')"

With Cnn.NewFieldDefs
      .Add "PID   Integer"
      .Add "FDate ShortDate"
  End With
Cnn.CreateTable "Absence_tbl"

   Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('1', '2023-03-19')"
   Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('2', '2023-03-22')"
   Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('3', '2023-03-10')"
   Cnn.Execute "INSERT INTO Absence_tbl (PID, FDate) VALUES ('1', '2023-03-21')"

I use this code to retrieve all pupils who are present today.

 Dim StrSql As String
   StrSql = "Select *  from Class_tbl " & _
" Where ID NOT IN (SELECT PID FROM Absence_tbl  " & _
  " Where FDate  = '" & Format(Date, "yyyy-mm-dd") & "')"

Do Until Rs.EOF
          Debug.Print Rs!PID, Rs!FDate
          Rs.MoveNext
        Loop

I'm getting the exact expected output.
1
3
4
5
6
Only one Record (ID = 2) is absent on 2023-03-22

Now I want to retrieve all the sessions in which of a particular pupil was present ( ID = 1)

StrSql = "Select *  from Class_tbl " & _
" Where ID NOT IN (SELECT PID FROM Absence_tbl  " & _
  " Where PID = 1) "

Do Until Rs.EOF
          Debug.Print Rs!PID, Rs!FDate
          Rs.MoveNext
        Loop

I'm wishing to to get this output:

1 ........... 2023-03-22
1 .......... 2023-03-10

Unfortunately I failed.
thank you

[Zvoni]

Don’t use subselects.
use a left join

EDIT: remove the NOT in your WHERE clause

[newbie2]

Don’t use subselects.
use a left join

EDIT: remove the NOT in your WHERE clause

thank you

StrSql = "Select * from Absence_tbl  " & _
" LEFT JOIN Class_tbl " & _
" on Class_tbl.ID = Absence_tbl.PID " & _
" where  ID = 1  "
Set Rs = Cnn.OpenRecordset(StrSql)

Do Until Rs.EOF
          Debug.Print Rs!PID, Rs!FDate
          Rs.MoveNext
        Loop

the output is this
1 ...............19/03/2023
1................21/03/2023

But I wish to get this:
1 ...............22/03/2023
1 ...............10/03/2023

thank you

[newbie2]

Maybe I'm just not getting what you're describing... but if you insert the record into your absence table when someone is absent, do you even need to worry about the class table with your select statement?

If you want only absences then don't they already exist within your absence table?

SELECT * FROM ABSENCE_TBL WHERE PID = 1

Would that not give you what you're looking for?

thank you
But I want to retrieve pupils who are present based on the absence table.

[Zvoni]

Oh boy. This setup is IMO all wrong.

i read pupils and classes, so this is a classroom scenario with absence and presence

you need a table just with (consecutive?) dates, your pupils-table and a third table connecting both in a m:m relation
this third table has foreign key to the dates and another foreign key to the pupils. A third column is a boolean indicating absence/presence.
easy enough

[newbie2]

Oh boy. This setup is IMO all wrong.

i read pupils and classes, so this is a classroom scenario with absence and presence

you need a table just with (consecutive?) dates, your pupils-table and a third table connecting both in a m:m relation
this third table has foreign key to the dates and another foreign key to the pupils. A third column is a boolean indicating absence/presence.
easy enough

I tried to apply this scenario but I'm lost.
May be because I did not understand this scenario well.
Briefly
I have a pupils table and Absence table
I can retrieve the list of absentees easily from the asence table.

But I'm trying to retrieve the list of attendees based on the absence table.
Then getting all the dates of attendence or presence of a particular pupil.
Can your scenario do the task?
thanks

[Zvoni]

But I'm trying to retrieve the list of attendees based on the absence table.
Then getting all the dates of attendence or presence of a particular pupil.

Based on what criteria?
If you only have a table, where a pupil is marked absent with its date, how do you think you're going to get anything for "presence" if there is no record?

Can your scenario do the task?

Yes. What database are you using? SQLite?

EDIT: For SQLite:
https://www.db-fiddle.com/f/xmnJixZD4cWzz2tsEmCPkA/1

As i said: Easy enough
In the fiddle on the right hand side pane you can play with the WHERE-Clause (lines 14+15)

[newbie2]

Zvoni
thak you for the link
I followed your approach
This is my Presence table
Attachment 187232

I can display the list of attendees on a grid easily
For this I use:

StrSql = "Select Fname,  Lname , " & _
" BirthDate, classe_name, subject from class_tbl" & _
 " left join Presence_tbl on class_tbl.ID = Presence.PID " & _
" left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
 " left join class_tbl on Presence_tbl.class_id = class_tbl.ID   " & _
  " Where Presence  = 1 and date_id = " & Lbl_Date(1).Caption
Set Rs = Cnn.OpenRecordset(StrSql)

However when I want to display the list of attendees with the number of sessions attended for each pupil, I can only get one record displayed on the grid.

StrSql = "Select Fname,  Lname , " & _
" BirthDate, classe_name, subject,  sum (presence) as presences from class_tbl" & _
 " left join Presence_tbl on class_tbl.ID = Presence.PID " & _
" left join subject_tbl on Presence_tbl.subj_id = subject.id " & _
 " left join class_tbl on Presence.class_id = class_tbl.ID   " & _
  " Where Presence  = 1 and date_id = " & Lbl_Date(1).Caption
Set Rs = Cnn.OpenRecordset(StrSql)

thank you

[Zvoni]

I don't remember anymore, but i think in SQLite if you don't specify a GROUP BY it sums up everything.
Add a GROUP BY Fname, Lname, Birthdate, class_name, subject after the WHERE