USQL demo join

customerorders.usql

DECLARE @customerscsv  string = "/Data/Meny/postgres_public_customer.csv";
DECLARE @orderlinescsv string = "/Data/Meny/postgres_public_order_line.csv";
DECLARE @productscsv  string = "/Data/Meny/postgres_public_product.csv";
DECLARE @orderscsv  string = "/Data/Meny/postgres_public_store_order.csv";

DECLARE @cvsout string = "/Data/Meny/Output/customerorders.csv";

@customers =
    EXTRACT Id          int,
            FirstName       string,
            LastName        string,
            Address         string,
            Phone	        string
    FROM @customerscsv
    USING Extractors.Csv();

@orders =
    EXTRACT Id          int,
            CustomerID       int          
    FROM @orderscsv
    USING Extractors.Csv();

@orderline = 
EXTRACT Id          int,
        OrderId    int,   
        ProductId  int,
        Quantity   int 
    FROM @orderlinescsv
    USING Extractors.Csv();


@products = 
EXTRACT Id      int,
        ProductName    string,   
        Price   int 
    FROM @productscsv
    USING Extractors.Csv(encoding: Encoding.UTF8);


@ds =
    SELECT c.Id, c.FirstName, c.LastName, p.ProductName, p.Price, ol.Quantity
    FROM @customers AS c 
    JOIN @orders AS o ON o.CustomerID == c.Id
    JOIN @orderline AS ol ON ol.OrderId == o.Id
    JOIN @products AS p ON p.Id == ol.ProductId;

OUTPUT @ds   
    TO @cvsout
      USING Outputters.Csv(encoding:Encoding.UTF8, quoting: false);