CODEWITHSUNDEEP
sqlduplicates
user760379
user760379

Reputation: 1

how can i eliminate duplicates in gridview?

i am retrieving data from three tables for my requirement so i wrote the following query

i was getting correct result but the problem is records are repeated whats the problem in

that query. i am binding result of query to grid view control. please help me 

SELECT DISTINCT (tc.coursename), ur.username, uc. DATE, 'Paid' AS Status
  FROM tblcourse tc, tblusereg ur, dbo.UserCourse uc
 WHERE tc.courseid IN (SELECT ur1.courseid
                         FROM dbo.UserCourse ur1
                        WHERE ur1.userid = @userid)
   AND ur.userid = @userid
   AND uc. DATE IS NOT NULL
   AND ur.course - id = uc.course - id

Upvotes: 0

Views: 776

Answers (2)

gbn
gbn

Reputation: 432271

There is no JOIN between tblcourse tc,tblusereg ur. So you get a cross join despite the IN (which is actually a JOIN)

DISTINCT works on the whole row too: not one column.

Note: you mention dbo.UserCourse twice but use different column names courseid and [course-id]

Rewritten with JOINs.

select distinct
    tc.coursename, ur.username, uc.[date], 'Paid' as [Status] 
from
    dbo.tblcourse tc
    JOIN
    dbo.tblusereg ur ON tc.courseid = ur.[course-id]
    JOIN
    dbo.UserCourse uc ON ur.[course-id] = uc.[course-id]
where
    ur.userid=@userid 
    and 
    uc.[date] is not null

This may fix your problem...

Upvotes: 3

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

Change that first part of your query 

select distinct (tc.coursename),

TO

select distinct tc.coursename,

to make all the columns distinct not just tc.coursename

Upvotes: 0

Related Questions