how to write query with subquery ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to write query with subquery ?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-15 : 12:02:09

Hi,

I'm trying to add a level of functionality to an existing application. I have a table called "comments"

Here users can comment on items.

I want to further this so that users can comment on comments that have been made on items.

I am curious as to how the most efficient way of querying this data would be. I could have a query in my web application that fires in the loop and checks on each comment, but I see that as highly inefficient and slow. It will probably kill my server because of the amount of loops, and connections generated.

If I do it in SQL, I'm not sure how to write it, OR how the data should look? What would an efficient resultSet look like ? I guess sort of structure so the front end could tell if the data was a comment, or a subcomment, so it could format it properly.

You can see examples on this on many standard sites such as youtube etc.

Are there any standard routines for this ?

tables ->

--table #1

CREATE TABLE [dbo].[tblUpdates](
[UpdateID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[Update] [varchar](250) NULL,
[updateDate] [datetime] NULL
) ON [PRIMARY]

-- table #2 (added table for sub comments)

CREATE TABLE [dbo].[tblUpdates_Comments](
[Update_commentID] [int] IDENTITY(1,1) NOT NULL,
[UpdateID] [int] NULL,
[commentFrom_userID] [int] NULL,
[comment] [varchar](1000) NULL,
[commentDate] [datetime] NULL
) ON [PRIMARY]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 12:04:26

Can you add a

commentSeq INT

column?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-15 : 12:09:41

Hi Peso,

Yes I think it would not be a problem to do whatever your recommendations are :) . I'm not sure where your going with this extra column tho ? Which table should it be added to ? And how would I use it ?? :)

Thanks again!
mike123

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 12:21:20

All you need is another column in tblUpdates_Comments:



CREATE TABLE [dbo].[tblUpdates_Comments](
[Update_commentID] [int] IDENTITY(1,1) NOT NULL,
[Parent_commentID] [int] NULL,
[UpdateID] [int] NULL,
[commentFrom_userID] [int] NULL,
[comment] [varchar](1000) NULL,
[commentDate] [datetime] NULL
) ON [PRIMARY]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 04:34:32

quote:

Originally posted by hanbingl

All you need is another column in tblUpdates_Comments:



CREATE TABLE [dbo].[tblUpdates_Comments](
[Update_commentID] [int] IDENTITY(1,1) NOT NULL,
[Parent_commentID] [int] NULL,
[UpdateID] [int] NULL,
[commentFrom_userID] [int] NULL,
[comment] [varchar](1000) NULL,
[commentDate] [datetime] NULL
) ON [PRIMARY]

I think what your hinting at is the "UpdateID" column thats already there. Please let me know if I am wrong....

I don't think this is where Peso was going with his contribution.

Thanks anyways :)

Mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 04:53:11

I think hanbingl was suggesting you to add a new field to indicate relation between comments and its subcomments

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 05:01:01

Hi visakh16,

Is this relationship not enough?

table #1
[UpdateID] [int] IDENTITY(1,1) NOT NULL,
table #2
[UpdateID] [int] NULL,

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-16 : 09:15:03

You need a previous comment ID on comments as hanbingl said so you can relate a comment back to another comment. You should also have a nullable FK constraint on it back to the same comments table. You should also have a nullable FK to the update table. Finally, you want a constraint so you can only have a value in either one, otherwise you may have a comment on a comment and an update, or neither.
That's your model sorted out I think and to query it you'll need a recursive CTE. I'm not in front of a DB now so you'll have to work that bit out yourself.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 09:34:29

Hi LoztInSpace,

Sorry for the confusion here.

I'm not following where you are going with the DB design completely here. Relating comments back to comments is that for threaded sub comments? If so, that might be a good option, I was just planning on have "comments" on the "updates", and I think my current db design allows for that ?

Perhaps my names are slightly confusing here, but I am still under the impression that I can do sub comments with this design? Please correct me if I am wrong, but I can not see the problem above.

"Comments" are to be made on "updates"
"updates" are in the first table, "comments" in the second table.
in the second table, we have the "updateID" so that the comment references which "update" it is commenting on.

Good points on the FK.

Not familiar with the termn "recursive CTE", but it sounds like thats the type of query needed, and what I need to accomplish.

Thanks again very much for all the input!
Mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:13:03

"updates" are in the first table, "comments" in the second table.
in the second table, we have the "updateID" so that the comment references which "update" it is commenting on.

using this you can distinguish which comments were made for which updates. but how would you track which comments were made in turn as response to other comments?or do you dont want to care about such an analysis? that was what we were trying to point out

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-16 : 13:24:39

quote:
Originally posted by visakh16

"updates" are in the first table, "comments" in the second table.
in the second table, we have the "updateID" so that the comment references which "update" it is commenting on.

using this you can distinguish which comments were made for which updates. but how would you track which comments were made in turn as response to other comments?or do you dont want to care about such an analysis? that was what we were trying to point out

Hi visakh16,

So you are talking about 2nd level comments ? (comments on comments?)

I initially wasn't going to accomodate for that, but if its not that hard to do, and not much of a performance hit, it could be a great thing to add! :)

I am open to either method. :)

Thanks again!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:27:07

quote:
Originally posted by mike123

quote:
Originally posted by visakh16

"updates" are in the first table, "comments" in the second table.
in the second table, we have the "updateID" so that the comment references which "update" it is commenting on.

using this you can distinguish which comments were made for which updates. but how would you track which comments were made in turn as response to other comments?or do you dont want to care about such an analysis? that was what we were trying to point out

Hi visakh16,

So you are talking about 2nd level comments ? (comments on comments?)

I initially wasn't going to accomodate for that, but if its not that hard to do, and not much of a performance hit, it could be a great thing to add! :)

I am open to either method. :)

Thanks again!
mike123

that was what you're told about recursive ctes. using it you can easily retrieve the hierarchy of comments if you also have a field called parent_comment_id in your table. Please note that for using CTEs yu must be in sql 2005 or later (i think you're already as you've posted in 2005 forum)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-17 : 01:29:29

Hi visakh16,

Yes I am running sql2005, and hope to move to 2008 shortly. Are CTE queries much more expensive ?

Thanks!
Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources