alaa

alaa

  • NA
  • 166
  • 84.8k

duplicate select rows even distnict

Aug 5 2014 6:36 PM
i have three tables with inner join when i make select it return duplicate rows i use distict and still return duplicate rows 

    TABLE [dbo].[Tbl_Comment](
[Comid] [int] IDENTITY(1,1) NOT NULL,
[bookid] [int] NULL,
[U_ID] [int] NULL,
[Comment] [nvarchar](max) NULL,
[Com_date] [datetime] NULL,
[puplished] [bit] NULL,
    CONSTRAINT [PK_Tbl_Comment] PRIMARY KEY CLUSTERED 

the second table is 

    TABLE [dbo].[Tbl_Reply](
[replyid] [int] IDENTITY(1,1) NOT NULL,
[Comid] [int] NULL,
[U_ID] [int] NULL,
[replymsg] [nvarchar](max) NULL,
[rep_datetime] [datetime] NULL,
[is_deleted] [bit] NULL,
    CONSTRAINT [PK_Tbl_Reply] PRIMARY KEY CLUSTERED 

my last one is

    TABLE [dbo].[Tbl_adminreply](
[adrep_id] [int] IDENTITY(1,1) NOT NULL,
[Comid] [int] NULL,
[adminreply] [nvarchar](max) NULL,
    CONSTRAINT [PK_Tbi_adminreply] PRIMARY KEY CLUSTERED 

tables Reply and Tbl_adminreply have join with comment table with comid 
when i make select it return duplicate rows ?

     SELECT  DISTINCT  Tbl_Comment.Comment, Tbl_Reply.replymsg, Tbl_Reply.rep_datetime, Tbl_adminreply.adminreply FROM Tbl_adminreply INNER JOIN Tbl_Comment ON Tbl_adminreply.Comid = Tbl_Comment.Comid INNER JOIN Tbl_Reply ON Tbl_Comment.Comid = Tbl_Reply.Comid  WHERE (Tbl_Comment.Comid = 3) ORDER BY Tbl_Reply.rep_datetime DESC

Answers (4)