0

How would I populate a view with a 3 table join

Suppose I had the following tables:

Customer(CustomerID, CustomerName)
    Order(OrderID, CustomerID)
    OrderItem(OrderID, ItemID, Description)

In SQL I could create Customer orders with:

select a.CustomerName, b.OrderID, c.ItemID, c.Description
    from Customer a, Order b, OrderItem c
    where a.CustomerID = b.CustomerID and b.OrderID = c.OrderID

In Ninox, it seems you have to iterate through result sets as follows:

for a in select Customer do
        for b in select Order where a.CustomerID = CustomerID do
            for c in select OrderItem where b.OrderID = OrderID do
                [OUTPUT?]  a.CustomerName, b.OrderID, c.ItemID, c.Description
            end
        end
    end;

Is there a better way of doing this programmatically?

How would I populate a View with the result set?

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • TNT
    • OregonMines
    • TNT
    • 2 yrs ago
    • Reported - view

    very much interested in this solution - thinking a discord users group would be handy for live discussions outside of webinar and to discuss further :)

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    As far as I know, Ninox does not support JOINs yet (the join() function is not the same as JOIN). If you want to do something similar in a View layout element, the only way I know how is by creating formula fields for your "N" tables in your "1" table and then you would the select statement in the view as you normally would, i.e., select Customer.

     

    I don't think you can specify which fields appear in a View programmatically. You have to use the UI to show/hide fields.

    Like
    • Paul
    • Paul
    • 2 yrs ago
    • Reported - view

    Here's Version 2 which provides the View...

     

    Given

    Customer(CustomerID, CustomerName)
        Order(OrderID, CustomerID)
        OrderItem(OrderID, ItemID, Description)

    Define the target table as

     
        Result(CustomerName, OrderID, ItemID, Description)

    delete select Result;             //empty Result table

    for a in select Customer do                  //populate the Result table
        for b in select Order where a.CustomerID = CustomerID do
            for c in select OrderItem where b.OrderID = OrderID do
                let z := (create Result);
                z.(CustomerName := a.CustomerName);
                z.(OrderID := b.OrderID);
                z.(ItemID := c.ItemID);
                z.(Description := c.Description)
            end
        end
    end;

    In a View Layout element set the Formula to

    select Result

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    Nice solution! So, now I'm curious. Since you have come up with this workaround, are you satisfied with steps necessary to make it work or would you like the View to support JOIN commands directly?

    Like
    • Paul
    • Paul
    • 2 yrs ago
    • Reported - view

    I don't really understand the syntax of Ninox SQL. It seems to be a bit of a mashup of procedural, objective and declarative languages. is it based on any common implementation? For example, why is the statment "z.(OrderID := b.OrderID)" rather than " z.OrderID := b.OrderID"? In the absence of further clarity over the syntax, I'd like standard SQL with SELECT, JOIN's, UPDATE, INSERT, etc..

     

    Also, I  initially tried to get this working with Result as a sub-table of Customer. Not sure why it wouldn't work.

    Like
    • Sean
    • Sean
    • 2 yrs ago
    • Reported - view

    "You're so analytical! Sometimes you just have to let art... flow... over you.". Hopefully you'll recognize the quote and that it's all in fun.

     

    When I started using Ninox, it was for my business, but now I'm hooked and enjoy tinkering around with it. I can't explain the syntax in your example so I just let it... flow... over me ;). I think the View layout element would be perfect for implementing standard SQL statements... no telling when or if it will be implemented. As for the sub-table, you could have the Order sub-table on the Customer form view, but you'd have to select an Order to drill down to OrderItem which would popup another form view.

    Like
Like Follow
  • 2 yrs agoLast active
  • 6Replies
  • 2130Views