I am now trying to use Metabase v0.29 to develop a question. Suppose I have three tables and would like to produce an output table shown below.
Table 1 - Job/Student ID (job_student_ids)
Job ID Student ID
J101 S101
J101 S102
J102 S101
Table 2 - Job Items (job_items)
Job ID Job Item
J101 Apple
J101 Orange
J102 Apple
J102 Banana
Table 3 - Student Items (student_items)
Student ID Student Item
S101 Apple
S101 Orange
S102 Pear
S102 Orange
S103 Apple
S103 Orange
Output Table - Item Matches
Job Item Student Item Match
Apple 3
Orange 2
Banana 1
Example explanation - how to work out Apple?
Notice both J101 and J102 request Apple (shown in Table 2), and J101 has applicants S101 and S102 (shown in Table 1) while J102 only has applicant S101.
J101: One match from applicant S101 who has an Apple, another match from applicant S102 who also has an Apple (shown in Table 3). Therefore 2 Apple matches.
J102: One match from applicant S101 who has an Apple.
Hence, in total 3 matches for Apple.
Please note: Due to the actual size of Table 1-3 on our database, it is impossible to append/union all of them together to make that count.
Is there any efficient way to achieve this goal? Thanks in advance - this is my first time to post a question here so please let me know if you'd like any clarifications.
