SQL query for returning nested categories in Google Cloud Spanner?-CodePudding

I have the following table in Google Cloud Spanner for categories.

CREATE TABLE categories (
  categoryId STRING(36) NOT NULL,
  name STRING(128) NOT NULL,
  parent BOOL NOT NULL,
  parentId STRING(36),
  archived BOOL,
  FOREIGN KEY (parentId) REFERENCES categories (categoryId)
) PRIMARY KEY(categoryId);

So the table might have entries as shown below.

categoryId	name	parent	parentId	archived
1	Clothing	true		false
2	Homeware	true		false
3	Shirts	false	1	false
4	Pants	false	1	false
5	Technology	true		false
6	Tablets	false	5	true

I want to query this table such that it returns firstly all the parent categories that aren't archived (I can't really delete rows in my application, as documents will reference rows - so I implemented archiving), along with all the child categories (not archived) belonging to said parents.

I came as far as writing the following query:

SELECT categories.categoryId, categories.name, childCategories.name AS childName 
FROM categories
LEFT JOIN categories AS childCategories
ON childCategories.parentId = categories.categoryId
WHERE categories.parent = true AND categories.archived = FALSE

However, this will return rows where child categories are archived. Below is the exact result returned.

categoryId	name	childName
1	Clothing	Shirts
1	Clothing	Pants
2	Homeware
5	Technology	Tablets

The desired result would be:

categoryId	name	childName
1	Clothing	Shirts
1	Clothing	Pants
2	Homeware
5	Technology

I have tried altering the query as:

SELECT categories.categoryId, categories.name, childCategories.name AS childName 
FROM categories
LEFT JOIN categories AS childCategories
ON childCategories.parentId = categories.categoryId
WHERE categories.parent = true AND categories.archived = FALSE AND childCategories.archived = FALSE

This, however, won't return any parent categories if they don't have at least one child category. Below is the exact result returned.

categoryId	name	childName
1	Clothing	Shirts
1	Clothing	Pants

I can't figure out how to write the query to obtain the desired result. Any help/insight would be appreciated. Thanks in advance!

CodePudding user response：

You were almost there, as you need only the child Categories which are not archived, Move the condition childCategories.archived = FALSE to ON instead of WHERE clause.

Putting the filter on WHERE will remove the result from Dataset, however if you put it in ON condition, will return everything from parent Categories and respective matching row from Child.

SELECT categories.categoryId, categories.name, childCategories.name AS childName 
FROM categories
LEFT JOIN categories AS childCategories
ON childCategories.parentId = categories.categoryId
AND childCategories.archived = FALSE --Added here
WHERE categories.parent = true AND categories.archived = FALSE