Rewriting a SQL query into SurrealQL

Question

I've been rewriting some old SQL problems from university in SurrealQL to learn surrealDB but after days of thinking I can't solve this problem:

Find all students and professors from the same department that share a birthday

This is how this can be solved in SQL:

select st.stname, pf.profname, st.stbmonth, st.stbday
 from STT st
 join PROFT pf on st.stdepid = pf.profdepid
and st.stbmonth = pf.profbmonth
and st.stbday = pf.profbday;

Here is the data that I'm using for SurrealDB

And This is my best Attempt:

select *, ->stdep->dep<-profdep<-prof from student;

This gives me, each student with a list of professors from the same department. the problem is there is no way to filter the professors' list based on the student.

a query that should work but doesn't:

select *, ->stdep->dep<-profdep<-prof[where profbday == stbday && profbmonth == stbmonth] from student;

It seems that you can't access student properties within professor's context.

I'm getting to the point where I think that not every query is possible in SurrealQL.

Answer 1

After days of tinkering I finally solved it!

select st.stname as stname, prof.profname as profname, st.stbmonth as bmonth, st.stbday as bday 
from (select id as st, ->stdep->dep<-profdep<-prof as prof 
from student split prof)
 where (prof.profbday == st.stbday) 
 and (prof.profbmonth == st.stbmonth);