I have three tables - Company, Sales, Purchases. The sales and purchase tables FK to the same Company table using its Entity ID. If I build a query with either the sales or purchase table as the starting point I can pull in attributes of the Company table either as filter, group etc. Thats great and works well, however If I start a query from the COMPANY table, references to either SALES or PURCHASES are not visible. When I click on a specific company record METABASE does display “This Company is connected to:” information.
My first question is: Is there anyway to expose that relationship (parent->child) when building a query where the parent is the source table? Thereby allowing the query to be potentially filtered based on attributes from multiple ‘child’ tables. e.g.list companies who have sold ‘cars’ and have purchased ‘ice cream’
My second question is: I can added a second column to the COMPANY and also the SALES/PURCHASE tables then FK the COMPANY to SALES/PURCHASE table. This exposes the latter (which now has two entity fields defined) in the query builder when COMPANY is selected as the source table. This appears to work but was hoping for a more elegant solution. (i.e. how best to exploit the bidirectional nature of the original FK relationship)