question

Unfortunately the query command can't automatically search a partitioned list. It can only search an unpartitioned global list. If it where an unpartitioned global list, the query would look something like:

query("SELECT SKU, demand FROM Status_SKUs, listInventory WHERE Status_SKUs.SKU = listInventory.SKU ORDER BY demand DESC");

I'd like to add some syntax to the query command where you could do something like listInventory@partitionID to define a specific partition to search. For now, though, you'll have to use the $ syntax to do it manually. This would be something like:

query("SELECT SKU, demand FROM Status_SKUs, $1 AS Inv WHERE Inv.$2 = SKU ORDER BY demand DESC", 
    listentries(globallist("listInventory"), partitionID), 
    getlabel(tonode(getnodenum($iter(1))), "SKU"));

Here's how it works. I want to dupe the query command into thinking I actually have a table when in reality, it may be just some model data somewhere that I can access. So, I tell the query command I've got a "table" designated by $1. Putting $1 in the FROM clause means I'm going to tell the query what the table is in the first additional parameter to the query command. So, as my first additional parameter, I put: listentries(globallist("listInventory"), partitionID). This is a reference to a node in the tree containing all the current entries in the list partition. Now, from query's point of view, at this point all it needs to know is the number of rows in that table. By the fact that I return a reference to a node, it will assume that the number of rows in the "table" are the number of subnodes of that node, or, the number of entries currently in that partition of the list.

The query command then goes through my $1 "table", and whenever it needs a value for a "row", it makes $iter(1) return the subnode of the list entries that is associated with the row that it currently wants a value on. Thus $iter(1) is the "iteration on $1", or in this case it will reference a specific entry node in the list. Given an entry node, since in our case the entry value is a reference to a token, I can get the token with tonode(getnodenum($iter(1))). Further, if I want to get a label on that token, I can use getlabel(tonode(getnodenum($iter(1))), "SKU"). That's what I put into $2, or the second additional parameter of the query command. The WHERE clause is WHERE Inv.$2 = SKU. I've aliased $1 as Inv, and Inv.$2 designates that $2 is part of the $1 (or Inv) table. Thus I can put the getlabel() expression into the second parameter, and it will call that whenever it needs to compare the SKU of the global table with my token's SKU label (or Inv.$2).

I realize that having read the last two paragraphs you now might be more confused than when you started. I'm attaching an example model that essentially does what I'm talking about, so hopefully that may help. It is a basic model where I just added a bunch of objects to a list and then had SKU labels on those objects, and then inner join on the list and the table. The script window at the bottom does the query and dumps the result to a table.

In setting this up I realized that the listentries() command syntax checker disallows an object reference as the partition ID, which is a bug. So I put it on partition 1.

This answer tells you how to inner join with a single partition of a list. If you want to inner join with all partitions of a list, another option is to use the $1x$2 table definition syntax, which is another layer in the $ syntax. So if you need that, let me know and I can extend the answer.

partitionedlisttableinnerjoin.fsm