I need a help in Backend Sql Server (I want the output like this)

Posted by Jenniffer under ASP.NET on 10/20/2012 | Points: 10 | Views : 1796 | Status : [Member] | Replies : 11

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

OUTPUT:

Master Table Content ID
1
2
Comment Child Table Content ID Comment ID
1 1
1 2
2 1
2 2
2 3

Reply Child Table Content ID Comment ID Reply ID
1 1 1
1 1 2
2 1 1
2 1 2
2 2 1
2 3 1

1. In first table - Content ID (int identity(1,1) primary key)

2. In Second table - Content ID (int primary key), Comment ID(int identity(1,1) primary key)

3. In Third table - Content ID (int primary key), Comment ID(int primary key) , Reply ID (int identity(1,1) primary key)

this is possible using Composite primary key, but i don't know to create a syntax using Composite primary key .... Please somebody help me out from this.......

Thanks & Regards
JENI

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Jayakumars on: 10/20/2012 [Member] [MVP] Bronze | Points: 25

0	hi Jeni try this Code USE [Test] GO /**** Object: Table [dbo].[T3] Script Date: 10/20/2012 15:37:36 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[T3]( [ContentID] [int] NOT NULL, [CommentID] [int] NOT NULL, [ReplyID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_T3] PRIMARY KEY CLUSTERED ( [ContentID] ASC, [CommentID] ASC, [ReplyID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /** Object: Table [dbo].[T2] Script Date: 10/20/2012 15:37:36 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[T2]( [ContentID] [int] NOT NULL, [CommentID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED ( [ContentID] ASC, [CommentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /** Object: Table [dbo].[T1] Script Date: 10/20/2012 15:37:36 ***/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[T1]( [ContentID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ( [ContentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jenniffer, if this helps please login to Mark As Answer*. \| Alert Moderator

hi
Jeni

try this Code


USE [Test]

GO

/****** Object:  Table [dbo].[T3]    Script Date: 10/20/2012 15:37:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[T3](

	[ContentID] [int] NOT NULL,

	[CommentID] [int] NOT NULL,

	[ReplyID] [int] IDENTITY(1,1) NOT NULL,

 CONSTRAINT [PK_T3] PRIMARY KEY CLUSTERED 

(

	[ContentID] ASC,

	[CommentID] ASC,

	[ReplyID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[T2]    Script Date: 10/20/2012 15:37:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[T2](

	[ContentID] [int] NOT NULL,

	[CommentID] [int] IDENTITY(1,1) NOT NULL,

 CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED 

(

	[ContentID] ASC,

	[CommentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[T1]    Script Date: 10/20/2012 15:37:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[T1](

	[ContentID] [int] IDENTITY(1,1) NOT NULL,

 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 

(

	[ContentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jenniffer, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jenniffer on: 10/20/2012 [Member] Starter | Points: 25

0	Thank u so much Mr.Jayakumar.... Thanks & Regards JENI Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 10/20/2012 [Member] [MVP] Bronze | Points: 25

0	hi Welcome JENI Post Your further questions here mark as ans if this correct. Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jenniffer on: 10/20/2012 [Member] Starter | Points: 25

0	hi I Executed ur query... and i inserted values too... but i'm getting the same output as before.... like this.... ContentID CommentID 1 1 1 2 2 3 3 4 but i need a output like this..... ContentID CommentID 1 1 1 2 2 1 3 1 Thanks & Regards JENI Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 10/20/2012 [Member] [MVP] Bronze | Points: 25

0	hi jeni primary key did not allow duplicate data look ur data here 1 1 1 2 2 3 3 4 but i need a output like this..... ContentID CommentID 1 1 1 2 2 1 3 1 here data 1 1 1 2 like this here duplicate value primary key did not allow duplicate. Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jenniffer on: 10/20/2012 [Member] Starter | Points: 25

0	hi JayaKumar I know primary key does't allow duplicates... but while inserting it accepting the duplicate values... I need the output like this.... ContentID CommentID 1 1 1 2 2 1 3 1 3 2 for this what can i do ? Thanks & Regards JENI Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 10/20/2012 [Member] [MVP] Bronze | Points: 25

0	hi jeni do u query output like this or data inserted like this? Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jenniffer on: 10/20/2012 [Member] Starter | Points: 25

0	Actually i'm using this concept for blog page.... If the user summit the comment for blog1, the date'll insert like this... ContentID CommentID 1 1 again another user comment for the same blog, the date'll insert like this... ContentID CommentID 1 1 1 2 again another user summit the comment for blog2, the date'll insert like this... ContentID CommentID 1 1 1 2 2 1..... this is the concept... which i used in blog page...... can u help me do this concept.....??? Thanks & Regards JENI Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Actually i'm using this concept for blog page....
If the user summit the comment for blog1, the date'll insert like this...
ContentID CommentID
1 1
again another user comment for the same blog, the date'll insert like this...
ContentID CommentID
1 1
1 2
again another user summit the comment for blog2, the date'll insert like this...
ContentID CommentID
1 1
1 2
2 1..... this is the concept... which i used in blog page...... can u help me do this concept.....???

Thanks & Regards
JENI

Jenniffer, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 10/20/2012 [Member] [MVP] Bronze | Points: 25

0	hi try this code Select ContentID,CommentID from T2 where ContentID in(Select ContentID from T2 where T2.ContentID>=1) Group by ContentID,CommentID Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 10/20/2012 [Member] [MVP] Bronze | Points: 25

0	hi try to count for this previous records can u sent clearly ur table design and ur requirement in my mail id. i ill sent Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jenniffer on: 10/20/2012 [Member] Starter | Points: 25

0	i have send my requirement in ur mail.... pls check it out.... Thanks & Regards JENI Jenniffer, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts