Filtering with lookup tables

Looker Q&A Looker
1

I’ve got a user who want’s to be able to input a child product id and see the list of that products parent as well as all of that other children that belong to it’s parent. I’m able to write the query out in sql with relative ease, but I’m unsure of how to create a looker explore that would allow for the user to do it.

SELECT
  P.ProductID [ChildProductID],
  P.ProductName [ChildProductName],
  PP.ParentProductID,
  PP.ParentName
FROM
  `Product` P
  LEFT JOIN `ParentProduct` PP ON P.ProductID = PP.ChildProductID
WHERE
  PP.ParentProductID IN (
    SELECT
      PP.ParentProductID
    FROM
      `ParentProduct` PP
    WHERE
      PP.SubProductID IN (3, 4)
  )

Example output would look like this

ChildProductID ChildProductName ParentProductID ParentProductName
3 Product 3 1 Product 1
5 Product 5 1 Product 1
4 Product 4 2 Product 2
8 Product 8 2 Product 2
2

First you need to choose which view will be your base view: the parent or the child view and then add a join to it.

Field picker will contain both, which will give you ability to filter by either parent or the child

3

That’s how the lookML is currently set up, the issue is that when I filter by child the result set only shows the individual record associated to that child, so if I filter by childid in (3,4) I get:

ChildProductID ChildProductName ParentProductID ParentProductName
3 Product 3 1 Product 1
4 Product 4 2 Product 2

I want to see all the children related to the parent of the children specified, so if I filter by childid in (3,4) I want to see:

ChildProductID ChildProductName ParentProductID ParentProductName
3 Product 3 1 Product 1
5 Product 5 1 Product 1
4 Product 4 2 Product 2
8 Product 8 2 Product 2

Child products 5 and 8 aren’t in my filter list because I don’t know that they are children of product 1 or 2, but I want to see them in my result set.

4

Not sure that can be done without using a SQL derived table, because there is a sub-query?

5

Thank you, that was kind of my assumption as well, but I thought I’d check with the community before saying so.