public void Join_Unequal()
 {
     SubSonic.SqlQuery q = new Select("productID", "categoryName")
         .From(Product.Schema)
         .NotEqualJoin(Category.Schema);
     string sql = q.BuildSqlStatement();
     Assert.AreEqual(
         "SELECT [dbo].[Products].[ProductID], categoryName\r\n FROM [dbo].[Products]\r\n JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] <> [dbo].[Categories].[CategoryID]\r\n",
         sql);
 }
 public void Select_BetweenAnd()
 {
     SubSonic.SqlQuery q = new Select("productid").From(Product.Schema).Where("productid").IsBetweenAnd(2, 5);
     string sql = q.BuildSqlStatement();
     Assert.AreEqual(
         "SELECT [dbo].[Products].[ProductID]\r\n FROM [dbo].[Products]\r\n WHERE [dbo].[Products].[ProductID] BETWEEN @ProductID0_start AND @ProductID0_end\r\n",
         sql);
 }
 public void Join_RightOuter()
 {
     SubSonic.SqlQuery q = new Select("productID", "categoryName")
         .From(Product.Schema)
         .RightOuterJoin(Category.Schema);
     string sql = q.BuildSqlStatement();
     Assert.AreEqual(
         "SELECT [dbo].[Products].[ProductID], categoryName\r\n FROM [dbo].[Products]\r\n RIGHT OUTER JOIN [dbo].[Categories] ON [dbo].[Products].[CategoryID] = [dbo].[Categories].[CategoryID]\r\n",
         sql);
 }
 public void Aggregate_Count()
 {
     SubSonic.SqlQuery q = new Select(
         Aggregate.Count("ProductID"),
         Aggregate.Sum("UnitPrice", "boots"),
         Aggregate.GroupBy("categoryID"))
         .From("Products").Where("CategoryID").IsGreaterThan(5)
         .OrderAsc("categoryID").OrderDesc("boots");
     string sql = q.BuildSqlStatement();
     Assert.AreEqual(
         "SELECT COUNT(ProductID) AS 'CountOfProductID', SUM(UnitPrice) AS 'boots', categoryID AS 'GroupByOfcategoryID'\r\n FROM [dbo].[Products]\r\n WHERE [dbo].[Products].[CategoryID] > @CategoryID0\r\n GROUP BY categoryID\r\n ORDER BY categoryID ASC,boots DESC\r\n",
         sql);
 }
 public void Join_Cross()
 {
     SubSonic.SqlQuery q = new Select("productID", "categoryName")
         .From(Product.Schema)
         .CrossJoin(Category.Schema);
     string sql = q.BuildSqlStatement();
     Assert.AreEqual(
         "SELECT [dbo].[Products].[ProductID], [dbo].[Categories].[CategoryName]\r\n FROM [dbo].[Products]\r\n CROSS JOIN [dbo].[Categories]\r\n",
         sql);
 }
 public void Join_Unequal()
 {
     SubSonic.SqlQuery q = new Select("productID", "categoryName")
         .From(Product.Schema)
         .NotEqualJoin(Category.Schema);
     string sql = q.BuildSqlStatement();
     Assert.AreEqual(
         "SELECT `main`.`Products`.`ProductID`, `main`.`Categories`.`CategoryName`\r\n FROM `main`.`Products`\r\n JOIN `main`.`Categories` ON `main`.`Products`.`CategoryID` <> `main`.`Categories`.`CategoryID`\r\n",
         sql);
 }
        public void Join_Cross()
        {
            SubSonic.SqlQuery q = new Select("productID", "categoryName")
                .From(Product.Schema)
                .CrossJoin(Category.Schema);
            string sql = q.BuildSqlStatement();
            
            //Assert.Fail("sql = " + sql);

            //SELECT `main`.`Products`.`ProductID`, `main`.`Categories`.`CategoryName`
	        // FROM `main`.`Products`
	        // CROSS JOIN `main`.`Categories`

            Assert.AreEqual(
                "SELECT `main`.`Products`.`ProductID`, `main`.`Categories`.`CategoryName`\r\n FROM `main`.`Products`\r\n CROSS JOIN `main`.`Categories`\r\n",
                sql);
        }