Sub queries using aml

I have been trying to figure out a way to do sub queries in aml without any luck

My use case is that I have an item type, that is populated with a whole lot of data (8+mill records). A simplified version of the item type is shown below

- Name string
- Value string
- SecondValue string

Here is an SQL version of the query I would like to have in AML:

SELECT * FROM MyItem WHERE Value IN (SELECT SecondValue FROM MyItem WHERE Name = 'MyName' AND Value = 'MyValue')

The reason why I would like it in AML is so the result will be show in the UI grid. Is this possible in AML? Or may there be a whole other way to accomplish this? Thanks