12.4 Judge existence of non-foreign key matching

 

Based on association of two tables, get records through judging existence of non-foreign key matching.
According to Score table and Student table, find the number of students in each class whose scores of a certain subject are above 80.

Score
StudentID
Subject
Score
Student
ID
Class
Name

Perform filtering with a subquery and then distinct according to the join field on Score table, and the field will be a primary key.

SPL script:

A
1 =connect(“db”)
2 =A1.query(“select * from Student”)
3 =A1.query@x(“select * from Score”)
4 =A3.select(Score>80)
5 =A4.id(StudentID)
6 =A2.join@i(ID, A5)
7 =A6.groups(Class; count(1):StudentCount)

A1 Connect to the database.
A2 Query Student table.
A3 Query Score table.
A4 Select Score records where scores are above 80.
A5 Use id() function to perform distinct on StudentID.
A6 Use A.join@i() function to perform filtering join.
A7 Group join result by Class and count students in each class.

Execution result:

Class StudentCount
Class 1 9
Class 2 11