Queries for Orders

1. Orders placed by customers from 'UK' OR 'Germany', AND the order date is after '1997-01-01', AND the Freight is greater than 50, OR the order was handled by an employee whose TitleOfCourtesy is 'Dr.'.

SQL Server Query 1

            
 SELECT  

     o.OrderID,  

     c.CompanyName,  

     c.Country,  

     o.OrderDate,  

     o.Freight,  

     e.FirstName,  

     e.LastName,  

     e.TitleOfCourtesy  

 FROM Orders o  

 JOIN Customers c ON o.CustomerID = c.CustomerID  

 JOIN Employees e ON o.EmployeeID = e.EmployeeID  

 WHERE  

     (  

         (c.Country = 'UK' OR c.Country = 'Germany') -- Customers from UK or Germany  

         AND o.OrderDate > '1997-01-01'              -- Order placed after specific date  

         AND o.Freight > 50                          -- Freight is expensive  

     )  

     OR  

     (e.TitleOfCourtesy = 'Dr.'); -- OR handled by an employee with 'Dr.' title  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("o.OrderID"," c.CompanyName"," c.Country"," o.OrderDate"," o.Freight"," e.FirstName"," e.LastName"," e.TitleOfCourtesy")  

     .From("Orders", "o")  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Customers","c")  

             .On(new Column("o.CustomerID").Equale(new Column("c.CustomerID"))),  

         new INNERJOIN().TableName("Employees","e")  

             .On(new Column("o.EmployeeID").Equale(new Column("e.EmployeeID")))  

     })  

     .Where(  

         new Where().StartBracket()  

             .StartBracket(new Column("c.Country").Equale("UK")).OR(new Column("c.Country").Equale("Germany")).EndBracket()  

             .AND(new Column("o.OrderDate").GreaterThan("1997-01-01"))  

             .AND(new Column("o.Freight").GreaterThan(50))  

             .EndBracket().OR(new Column("e.TitleOfCourtesy").Equale("Dr."))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT o.OrderID,
       c.CompanyName,
       c.Country,
       o.OrderDate,
       o.Freight,
       e.FirstName,
       e.LastName,
       e.TitleOfCourtesy
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     Employees AS e
     ON o.EmployeeID = e.EmployeeID
WHERE ((c.Country = @pMAIN_2507192101282688520
        OR c.Country = @pMAIN_2507192101282688521)
       AND o.OrderDate > @pMAIN_2507192101282688522
       AND o.Freight > @pMAIN_2507192101282688523)
      OR e.TitleOfCourtesy = @pMAIN_2507192101282688524;


            
        

Parameters (If used)

Query Results 1: