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
alaaPosted Aug 7, 2014, 3:22 AM
Khan Abrar AhmedPosted Aug 7, 2014, 2:44 AM
SELECT DISTINCT Tbl_Comment.Comment
,Tbl_Reply.replymsg
,cast(Tbl_Reply.rep_datetime as date)
,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 cast(Tbl_Reply.rep_datetime as date) DESC
Please mark as Answer if it help you
alaaPosted Aug 7, 2014, 2:28 AM
Guest UserPosted Aug 5, 2014, 9:05 PM