Published on November 17, 2020.

Many of my NetSuite clients - and especially those in the promotional products industry - manage their items as matrix items. This makes it easier for them to work with items that are available in various options, such as a t-shirt that is available in multiple size and color combinations.

Matrix items consist of a parent item and one or more child items (or "subitems"). Parent items are only used to relate the child items, and they are not orderable - so you won't see them on sales orders, purchase orders, or other transactions.

Querying for matrix items is relatively easy. Let's take a look at a few queries.

Parent Items

Let's start with a query that returns all parent items.

SELECT
	Item.ID,
	Item.ItemID,
	( SELECT COUNT(*) FROM Item AS ChildItem WHERE ChildItem.Parent = Item.ID ) AS ChildCount
FROM
	Item
WHERE
	( Parent IS NULL )
	AND ( IsInactive = 'F' )
	AND ( EXISTS ( SELECT * FROM Item AS ChildItem WHERE ChildItem.Parent = Item.ID ) )

In this query, I'm using two correlated subqueries. The first is used in the SELECT clause, and I'm using it to get the number of child items for each parent item. The second is used in the WHERE clause to return only items that really are parent items.

Child Items

Now let's query for a child item and its parent.

	
SELECT
	Item.ID,
	Item.ItemID,
	Item.FullName,
	Item.Description,
	ParentItem.ID AS ParentID,
	ParentItem.ItemID AS ParentItemID,
	ParentItem.FullName AS ParentItemFullName
FROM
	Item
	INNER JOIN Item AS ParentItem ON
		( ParentItem.ID = Item.Parent )
WHERE
	( Item.ID = 9999 )
	AND ( Item.IsInactive = 'F' )

In this query, I'm using a "self join" on the Item table, to join a child item to its parent. Both child and parent items are stored in the Item table, and the ID of a child item's parent is stored in a column named "Parent." So I'm using an INNER JOIN to join from the child record to its parent item record.

All Child Items for a Specific Parent

The previous query made it possible to specify a child item (based on its internal ID) and join to its parent item. Here's another query that returns all child items for a specified parent item.

	
SELECT
	Item.ID,
	Item.ItemID,
	Item.FullName,
	Item.Description
FROM
	Item
WHERE
	( Item.Parent = 9998 )
	AND ( Item.IsInactive = 'F' )

Standalone Items

Let's take a look at one last query. This one returns what I refer to as "standalone" items, which exist entirely on their own, independent of a parent or child item.

SELECT
	Item.ID,
	Item.ItemID
FROM
	Item
WHERE
	( Parent IS NULL )
	AND ( IsInactive = 'F' )
	AND ( NOT EXISTS ( SELECT * FROM Item AS ChildItem WHERE ChildItem.Parent = Item.ID ) )

Once again, I'm using a correlated subquery in the WHERE clause. But in this case, I'm using it as part of a "NOT EXISTS" predicate, indicating that only items that are not the parent of other items should be returned.

Wrapping Up

As I mentioned above, querying for matrix items using SuiteQL is relatively easy. Both child and parent items are stored in the same Item table, so depending on what you're trying to achieve, you'll likely need a combination of joins and/or subqueries.

Do you have a question about SuiteQL? Feel free to contact me, or join the conversation over on the NetSuite Professionals Slack Community.