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 : 1143 | Status : [Member] | Replies : 11
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response