Queries for Suppliers

1. Find Suppliers who supply at least one product with 'UnitPrice' between 10 and 20, AND at least one product with 'UnitsOnOrder' greater than 0, AND do NOT supply any product that has been discontinued.

SQL Server Query 1

            
 SELECT  

     s.SupplierID,  

     s.CompanyName,  

     s.ContactName  

 FROM Suppliers s  

 WHERE  

     -- Condition 1: Supplies at least one product with UnitPrice between 10 and 20  

     EXISTS (  

         SELECT 1  

         FROM Products p  

         WHERE p.SupplierID = s.SupplierID  

           AND p.UnitPrice BETWEEN 10 AND 20  

     )  

     AND  

     -- Condition 2: Supplies at least one product with UnitsOnOrder greater than 0  

     EXISTS (  

         SELECT 1  

         FROM Products p2  

         WHERE p2.SupplierID = s.SupplierID  

           AND p2.UnitsOnOrder > 0  

     )  

     AND  

     -- Condition 3: Does NOT supply any product that has been discontinued  

     NOT EXISTS (  

         SELECT 1  

         FROM Products p3  

         WHERE p3.SupplierID = s.SupplierID  

           AND p3.Discontinued = 1  

     );  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("s.SupplierID"," s.CompanyName"," s.ContactName")  

     .From("Suppliers","s")  

     .Where(  

         new Where(new EXISTS(new SqlQueryBuilder().Select()  

             .ColumnWithValue(1, "Col1")  

             .From("Products","p")  

             .Where(new Where(new Column("p.SupplierID").Equale(new Column("s.SupplierID")))  

                 .AND(new BETWEEN(new Column("p.UnitPrice"),10,20)))  

         )).AND(new EXISTS(new SqlQueryBuilder().Select()  

             .ColumnWithValue(1, "col1")  

             .From("Products", "p2")  

             .Where(  

                 new Where(new Column("p2.SupplierID").Equale(new Column("s.SupplierID")))  

                     .AND(new Column("p2.UnitsInOrder").GreaterThan(0))  

             )  

         )).AND(new EXISTS(new SqlQueryBuilder().Select()  

             .ColumnWithValue(1, "col1")  

             .From("Products", "p3")  

             .Where(  

                 new Where(new Column("p3.SupplierID").Equale(new Column("s.SupplierID")))  

                     .AND(new Column("p3.Discontinued ").Equale(1))  

             )  

         ))  

     )  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT s.SupplierID,
       s.CompanyName,
       s.ContactName
FROM Suppliers AS s
WHERE EXISTS (SELECT @pMAIN_2507200219162299360 AS Col1
              FROM Products AS p
              WHERE p.SupplierID = s.SupplierID
                    AND p.UnitPrice BETWEEN @pMAIN_2507200219162299361 AND @pMAIN_2507200219162299362)
      AND EXISTS (SELECT @pMAIN_2507200219162299363 AS col1
                  FROM Products AS p2
                  WHERE p2.SupplierID = s.SupplierID
                        AND p2.UnitsOnOrder > @pMAIN_2507200219162299364)
      AND EXISTS (SELECT @pMAIN_2507200219162299365 AS col1
                  FROM Products AS p3
                  WHERE p3.SupplierID = s.SupplierID
                        AND p3.Discontinued = @pMAIN_2507200219162299366);


            
        

Parameters (If used)

Query Results 1: