Correlated subqueries?

[ntwilson]

This maybe fixes #48. It tries to implement option number 2 as referenced in that issue. It lets you write a correlated subquery like

            let maxOrderQty = 
                select {
                    for d in table<Sales.SalesOrderDetail> do
                    correlate od in correlatedTable<Sales.SalesOrderDetail>
                    where (d.ProductID = od.ProductID)
                    select (maxBy d.OrderQty)
                }

            let query = 
                select {
                    for od in orderDetailTable do
                    where (od.OrderQty = subqueryOne maxOrderQty)
                    orderBy od.ProductID
                    select (od.SalesOrderID, od.ProductID, od.OrderQty)
                }

My main gripe here is that I'm pretty sure the alias used in the subquery has to match the alias in the parent query, but I don't know if there's a way around that. Maybe it could just be documented?

Does this seem like an acceptable solution? Would you like me to take a stab at updating the README?

[JordanMarr]

Wow, great work on this!

My main gripe here is that I'm pretty sure the alias used in the subquery has to match the alias in the parent query, but I don't know if there's a way around that. Maybe it could just be documented?

Yeah, I don't see a way around the fact that the user has to manually ensure that the table alias name has to exactly match the parent alias.

Does this seem like an acceptable solution? Would you like me to take a stab at updating the README?

Yes, this looks great and doesn't seem to add too much complexity to the codebase.

[JordanMarr]

Good idea using IsLikeZip instead of IsLikeJoin.

I also like that you can just use a regular table instead of a correlatedTable:

let latestOrderByCustomer = 
    select {
        for d in table<main.SalesOrderHeader> do
        correlate od in table<main.SalesOrderHeader>
        where (d.CustomerID = od.CustomerID)
        select (maxBy d.OrderDate)
    }

Which means that if you are using the new table declarations feature, you could write it like this:

let latestOrderByCustomer = 
    select {
        for d in main.SalesOrderHeader do
        correlate od in main.SalesOrderHeader
        where (d.CustomerID = od.CustomerID)
        select (maxBy d.OrderDate)
    }

[JordanMarr]

Published as v1.2.1. Thanks for the hard work! btw, I tweaked the readme example to use the new generated table declarations.

[ntwilson]

Published as v1.2.1

Thank you for your quick response as always. You're a fabulous maintainer to work with!