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 |
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
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.
Not sure that can be done without using a SQL derived table, because there is a sub-query?
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.