Let's say you wish to list all students in a university, and then, show all other students with the same birth year and month. So, you'd start with ``/student`` and then you could select all columns using the asterisk. Next, you could use the ``fork()`` operation to join to the same table based on a set of columns that are correlated.
/student{, /fork(year(dob),month(dob))}
http://demo.htsql.org/student%7B*,%20/fork%28year%28dob%29,m...
You could wrap this up in a definition to make it a bit easier to understand:
/student
.define(students_same_month := fork(year(dob),month(dob)))
.select(, /students_same_month )
http://demo.htsql.org/student%0A.define%28students_same_mont...
With the more general case, let's say you're interested in listing per semester, which students started during that semester. In this case, you start with ``/semester`` and then define the correlated set, ``starting_students`` which uses the ``@`` attachment operator to link the two sets. Then, you'd select columns from semester and the list of correlated starting students.
/semester
.define(starting_students:=
(student.start_date>=begin_date&
student.start_date<=end_date)@student)
{, /starting_students }
http://demo.htsql.org/semester.define%28starting_students:=%...
Typically, you'd include the new link definition, ``starting_students`` in your configuration file so that you don't have to define it again... then the query is quite intuitive:
/semester{, /starting_students }
While this all may seem complex, it's not an easy problem. More importantly, HTSQL has the notion of "navigation". You're not filtering a cross product of semesters and students, instead, you're defining a named linkage from each semester to a set of students. The rows returned in the top level of query are semesters and the rows returned in the nested level are students.
/student{, /fork(year(dob),month(dob))}
http://demo.htsql.org/student%7B*,%20/fork%28year%28dob%29,m...
You could wrap this up in a definition to make it a bit easier to understand:
/student .define(students_same_month := fork(year(dob),month(dob))) .select(, /students_same_month )
http://demo.htsql.org/student%0A.define%28students_same_mont...
With the more general case, let's say you're interested in listing per semester, which students started during that semester. In this case, you start with ``/semester`` and then define the correlated set, ``starting_students`` which uses the ``@`` attachment operator to link the two sets. Then, you'd select columns from semester and the list of correlated starting students.
/semester .define(starting_students:= (student.start_date>=begin_date& student.start_date<=end_date)@student) {, /starting_students }
http://demo.htsql.org/semester.define%28starting_students:=%...
Typically, you'd include the new link definition, ``starting_students`` in your configuration file so that you don't have to define it again... then the query is quite intuitive:
/semester{, /starting_students }
While this all may seem complex, it's not an easy problem. More importantly, HTSQL has the notion of "navigation". You're not filtering a cross product of semesters and students, instead, you're defining a named linkage from each semester to a set of students. The rows returned in the top level of query are semesters and the rows returned in the nested level are students.