Fetching data from db based on parent's partent table

[matKa]

I have three tables in the DataBase as follows:

StudentId in Maths Table is a FK to Id in Students Table.

TeacherId in Students Table is FK to Id in Teachers Table.

The Maths Table has enourmous amount of records and I want to work only on records of students whose teacher is Ivan.

By searching a bit I figured I could do it this way but it wasn't working:

LOAD Id,
Name;

[Teachers]:
SELECT Id,
Name
FROM db.Teachers;

LOAD Id,
Name,
TeacherId;

[Students]:
SELECT Id,
Name,
TeacherId
FROM db.Students;
inner join Select * FROM Teachers Where Name = 'Ivan';

LOAD Id,
StudentId,
Grade;

[Maths]:
SELECT Id,
StudentId,
Grade
FROM db.Maths;
inner join Select * FROM Students;

Like I said I wish to have Table Maths as it is but only with records that are of students whose teacher is Ivan

I don't understand how Load and Select works in Qlik. I won't be using it in future so any help would be really appriciated.

Edit1: missed one Load.

[marcus_sommer · Accepted solution]

Qlik doesn't execute any SQL else it transferred it per driver to the database and received then any return. If like you says the amount of data in the database enourmous and you need only a quite small part of it - it would be sensible to do the filtering already on the sql-side (with appropriate where clauses and/or inner joins) and not to transfer all data to Qlik and to filter the data there.

If your data aren't so huge that you couldn't process and transfer them within a practically time-frame you could just load them all to Qlik and then using exists() to filter the data, maybe in this way (if already all data are loaded to Qlik):

load Id as TeacherId from Teachers where Name = 'Ivan';
load Id as StudentId from Students where exists(TeacherId);
load * from Maths where exists(StudentID);

whereby I suggest to check before you applies any filtering on the data if you couldn't load all data into your application. With a well-build datamodel and avoiding complex expressions (if-loops and aggr-functions) a Qlik application could handle really large amounts of data / billions of records.

- Marcus