Jump to content
  • 0

Referencing the same table twice in a view.


KenWi

Question

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

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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."
image.thumb.png.4864cdd047d482989c8dc157c7257eb1.png

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




I hope that helps!

Link to comment
Share on other sites

  • 0
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.

Link to comment
Share on other sites

  • 0

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. 

 

Link to comment
Share on other sites

  • 0
On 9/5/2024 at 6:48 PM, NiceDuck said:

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. 

 

Thanks for the Attempt, the view will still display only 1 Person_Name 😕 (Tried this previously.)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...