Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

May 6, 2024

Feb 5, 2015

Query samples on a self referenced table (Part-4)

Leave a comment
In previous post we joined tables but there was a problem. We can not see "Help" menu which has not any sub menu. To solve the problem we will use Outer Join.

Linq

var menus = from t in Menus
  where t.MenuType==1
  join s in Menus on t.MenuID equals s.MenuParent into Subs
  from ss in Subs.DefaultIfEmpty()
  select new {
   TopMenu = t.Title,
   SubMenu = ss == null ? null : ss.Title
  };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .GroupJoin (
      Menus, 
      t => (t.MenuID), 
      s => s.MenuParent, 
      (t, Subs) => 
         new  
         {
            t = t, 
            Subs = Subs
         }
   )
   .SelectMany (
      temp0 => temp0.Subs.DefaultIfEmpty (), 
      (temp0, ss) => 
         new  
         {
            TopMenu = temp0.t.Title, 
            SubMenu = (ss == null) ? null : ss.Title
         }
   )

SQL

DECLARE @p0 Int = 1

SELECT [t0].[Title] AS [TopMenu], 
    (CASE 
        WHEN [t2].[test] IS NULL THEN NULL
        ELSE CONVERT(NVarChar(MAX),[t2].[Title])
     END) AS [SubMenu]
FROM [Menu] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[Title], [t1].[MenuParent]
    FROM [Menu] AS [t1]
    ) AS [t2] ON ([t0].[MenuID]) = [t2].[MenuParent]
WHERE [t0].[MenuType] = @p0

Jan 23, 2015

Query samples on a self referenced table (Part-3)

Leave a comment
In previous post we queried tables.
In this post we will join the tables. This will show Top Menu title and Sub Menu title in a row.

Linq

var menus = from t in Menus
  where t.MenuType == 1
  join s in Menus on t.MenuID equals s.MenuParent
  select new {
   TopMenu = t.Title,
   SubMenu = s.Title
  };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .Join (
      Menus, 
      t => (Int32?)(t.MenuID), 
      s => s.MenuParent, 
      (t, s) => 
         new  
         {
            TopMenu = t.Title, 
            SubMenu = s.Title
         }
   )

SQL

DECLARE @p0 Int = 1

SELECT [t0].[Title] AS [TopMenu], [t1].[Title] AS [SubMenu]
FROM [Menu] AS [t0]
INNER JOIN [Menu] AS [t1] ON ([t0].[MenuID]) = [t1].[MenuParent]
WHERE [t0].[MenuType] = @p0

Jan 13, 2015

Query samples on a self referenced table (Part-2)

Leave a comment
In previous post we created a database table, filled it with sample data and saw some basic queries.
In this post we will query the table in { Menu.Title, IEnumerable<menu> } form. This will show Top menu title and sub menu items in a row.

Linq

var menus = from t in Menus 
 where t.MenuType==1 
 select new {
  t.Title,
  SubMenus = from s in Menus
  where t.MenuType==1 && t.MenuID==s.MenuParent
  select s
 };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .Select (
      t => 
         new  
         {
            Title = t.Title, 
            SubMenus = Menus
               .Where (s => ((t.MenuType == 1) && ((Int32?)(t.MenuID) == s.MenuParent)))
         }
   )

SQL

DECLARE @p0 Int = 1
DECLARE @p1 Int = 1

SELECT [t0].[Title], [t1].[MenuID], [t1].[Title] AS [Title2], [t1].[Url], [t1].[MenuParent], [t1].[MenuType], (
    SELECT COUNT(*)
    FROM [Menu] AS [t2]
    WHERE ([t0].[MenuType] = @p0) AND (([t0].[MenuID]) = [t2].[MenuParent])
    ) AS [value]
FROM [Menu] AS [t0]
LEFT OUTER JOIN [Menu] AS [t1] ON ([t0].[MenuType] = @p0) AND (([t0].[MenuID]) = [t1].[MenuParent])
WHERE [t0].[MenuType] = @p1


Jan 11, 2015

Query samples on a self referenced table (Part-1)

Leave a comment
I will give you some query examples in Linq, Lambda Expression and SQL syntax.
Examples are build on one table that includes self referenced data. We will create a menu table on the database and fill it with some sample data. Some menu items are top menus and the others are subs. Sub menu items has a parent id which is referenced another menu item on the same table.

Step 1: Create menu table on SQL Server.
CREATE TABLE [dbo].Menu(
 [MenuID] [int] IDENTITY(1,1) NOT NULL,
 [Title] [nvarchar](50) NULL,
 [Url] [nvarchar](100) NULL,
 [MenuParent] [int] NULL, --If has parent fill this column with MenuID 
 [MenuType] [int] NULL --1: Top menu, 2:Sub menu
)

Step 2: Insert some data 
INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Accounts', '.aspx', 0, 1) --ID=1

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Users', '.aspx', 1, 2) --ID=2

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Customers', '.aspx', 1, 2) --ID=3

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Settings', '.aspx', 0, 1) --ID=4

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('App', '.aspx', 4, 2) --ID=5

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Web', '.aspx', 4, 2) --ID=6

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Service', '.aspx', 4, 2) --ID=7

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Parameters', '.aspx', 0, 1) --ID=8

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Account Type', '.aspx', 8, 2) --ID=9

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Help', '.aspx', 0, 1) --ID=10

Step 3: Query table. In this example we will see some basic queries.

Basic Queries

Queries below selects top menus and sub menus.

Linq

var topMenus = from f in Menus where f.MenuType==1 select f;
var subMenus = from f in Menus where f.MenuType==2 select f;

Lambda

Menus
   .Where (f => (f.MenuType == 1))

Menus
   .Where (f => (f.MenuType == 2))

SQL

DECLARE @p0 Int = 1
SELECT [t0].[MenuID], [t0].[Title], [t0].[Url], [t0].[MenuParent], [t0].[MenuType]
FROM [Menu] AS [t0]
WHERE [t0].[MenuType] = @p0
GO

DECLARE @p1 Int = 2
SELECT [t0].[MenuID], [t0].[Title], [t0].[Url], [t0].[MenuParent], [t0].[MenuType]
FROM [Menu] AS [t0]
WHERE [t0].[MenuType] = @p1
GO

See Part-2 for the other queries.

Nov 21, 2014