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 : 1033 | 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

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

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

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

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

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

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