Filtering with lookup tables

[cslama-16529040]

 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