Queries for Shippers

1. Shippers whose CompanyName contains 'Express' AND their Phone number starts with '(503)', OR suppliers who are from 'USA' AND have a HomePage defined.

SQL Server Query 1

            
 SELECT  

     'Shipper' AS EntityType,  

     s.CompanyName AS Name,  

     s.Phone AS ContactInfo  

 FROM Shippers s  

 WHERE s.CompanyName LIKE '%Express%' AND s.Phone LIKE '(503)%'  

 UNION ALL  

 SELECT  

     'Supplier' AS EntityType,  

     sup.CompanyName AS Name,  

     sup.HomePage AS ContactInfo  

 FROM Suppliers sup  

 WHERE sup.Country = 'USA' AND sup.HomePage IS NOT NULL;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column("Shipper", "EntityType")  

     .Column("s.CompanyName", "Name")  

     .Column("s.Phone","ContactInfo")  

     .From("Shippers", "s")  

     .Where(  

         new Where(new LIKE(new Column("s.CompanyName"), "%Express%")).AND(new LIKE(new Column("s.Phone"), "(503)%") )  

     )  

     .UnionAll(new SqlQueryBuilder().Select()  

         .Column("Supplier", "EntityType")  

         .Column("sup.CompanyName","Name")  

         .Column("sup.HomePage","ContactInfo")  

         .From("Suppliers","sup")  

         .Where(new Where(new Column("sup.Country").Equale("USA")).AND(new IS_NOT_NULL(new Column("sup.HomePage"))))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT @pMAIN_2507192044345721020 AS EntityType,
       s.CompanyName AS Name,
       s.Phone AS ContactInfo
FROM Shippers AS s
WHERE s.CompanyName LIKE @pMAIN_2507192044345721021
      AND s.Phone LIKE @pMAIN_2507192044345721022
UNION ALL
SELECT @pMAIN_2507192044345721023 AS EntityType,
       sup.CompanyName AS Name,
       sup.HomePage AS ContactInfo
FROM Suppliers AS sup
WHERE sup.Country = @pMAIN_2507192044345721024
      AND sup.HomePage IS NOT NULL;


            
        

Parameters (If used)

Query Results 1: