CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nour Berro
Nour Berro

Reputation: 560

get rows and rows from one table SQL server

i have comments table in sql server structured as 

CREATE TABLE [dbo].[LS_Commentes](
    [CommentId] [int] IDENTITY(1,1) NOT NULL,
    [OwnerId] [uniqueidentifier] NULL,
    [OwnerName] [nvarchar](50) NULL,
    [Email] [nvarchar](250) NULL,
    [Date] [nvarchar](15) NULL,
    [ParentId] [int] NULL,
    [CommentText] [nvarchar](400) NULL,
    [ItemId] [int] NULL,
    [upVotes] [int] NULL,
    [downVotes] [int] NULL,
    [isApproved] [bit] NULL,
 CONSTRAINT [PK_LS_MsgCommentes] PRIMARY KEY CLUSTERED 
(
    [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]

and i have sample data like this:

CommentId   OwnerId OwnerName   Email   Date    ParentId    CommentText ItemId  upVotes downVotes   isApproved
1   NULL    Test Commneter  NULL    1/4/2013    NULL    test    9   0   0   NULL
2   NULL    Test Commneter  NULL    1/4/2013    1   test    NULL    0   0   NULL
3   NULL    Test Commneter  NULL    1/4/2013    1   test    NULL    0   0   NULL

i want to write one query can get me all rows have itemid =9 and rows have parentid= comment id that selected (because itemid = 9)

look here i can solve it by adding item id 9 to the sub comments too but i just want to know if that could be solved without adding item id to comments and sub comments

Upvotes: 0

Views: 113

Answers (3)

Dog Ears
Dog Ears

Reputation: 10025

Would a recursive Common Table Expression give you the results you're after?

;with cte as 
(
    --Anchor
    select 
        commentid,
        ParentId
    from 
        LS_Commentes
    where
        ItemId = 9
    union all
    --Recursive member
    select 
        c.commentId,
        c.ParentId
    from
        LS_Commentes c join cte on c.ParentId = cte.CommentId

)
select * from cte

If you want to include more columns in the results ensure that both parts (the Anchor and recursive member) have identical columns.

Explanation: The anchor part (the first select) of a recursive query selects all rows where ItemId = 9, the second part uses the existing records in the result to include further records that satisfy it's criters (ParentId = cte.CommentId) this keeps going until nothing more is selected. And then the entire results must be selected at the end (after the CTEs definition)

Upvotes: 1

Dávid Tóth
Dávid Tóth

Reputation: 3245

I think it would be good with an embedded SQL query

 SELECT * 
 FROM `LS_Commentes` 
 WHERE `ItemId` = '9' 
     AND `ParentID`= (SELECT `CommentID` FROM `LS_Commentes` WHERE `ItemId` = 9);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think the following query does what you want:

select *
from ls_comments c
where c.itemID = 9 or
      c.parentID in (select c2.commentId from ls_comments c2 where c2.itemId = 9)

Upvotes: 1

Related Questions