Referencing the same table twice in a view.

[KenWi]

Dear Caspio Users,

I am attempting to do something which while I believe is a smiple join, Caspio doesn't seem to want to return any records on

As an Example, given the following 4 tables

Person (Contains a field NAME)

Student

Teacher

Class

All of these tables join on unique IDs so given the following joins

Teacher <-> Person

Student <-> Person

Class <-> Teacher

Class <-> Student

 

Is there a way to get Names to display for Teachers and Students in a Caspio View?

 

(Hopefully someone has encountered this and has found an easier way to work with it)

 

Thank you,

Ken

[nyunyiboy]

Hi @KenWi,

I recommend starting by creating table relationships. For instance, one teacher can have multiple class, and each class can have multiple students. This will help you identify which students belong to which class and who their teachers are when you create a view.

Here are some helpful links that might assist you with Table Relationships:
https://howto.caspio.com/tables-and-views/relationships/database-relationships/
https://howto.caspio.com/tables-and-views/relationships/relationship-settings/

If you want to create a view, set up a criteria like this: it will filter all students who belong to "Class 1" and have a teacher whose first name is "John."

Here are some helpful tech tips regarding Views: 
https://howto.caspio.com/tables-and-views/what-are-views/views/

I hope that helps!

[KenWi]

21 hours ago, nyunyiboy said:

Hi @KenWi,

I recommend starting by creating table relationships. For instance, one teacher can have multiple class, and each class can have multiple students. This will help you identify which students belong to which class and who their teachers are when you create a view.

I hope that helps!

Unfortunately it does not. My issue is not with creating the view, but in having the view join the same table in two separate ways. I know how to do this trivially in SQL, however I can't seem to find a way to make Caspio do it. It seems that my solution is to just de-normalize the data.

[NiceDuck]

The idea seems possible.

Though I'm afraid that the line/column for the students details and the details for Teachers will line up in different columns.

The idea I have in mind are the following

Person (all records) > Teachers (matching only)
Person (all records) > Students (matching only)
Teachers (all records) > Class (matching only)
Students (all records) > Class (matching only)

This would basically a bunch of left join though I visualized the setup through Task/trigger and not view.

Anyway, I hope it helps.