Filtering with lookup tables

cslama-16529040 · 10-18-2022 02:08 PM

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

Dawid

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

cslama-16529040

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

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.

kuopaz

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

cslama-16529040

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

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