Duplicate Records in a View

Hi There
I am having a problem with some SQL and hope someone out there can help. I
am fairly new so apologise any bad coding etc.
I have created a View, which works fine except I need to eliminate some
duplicate entries and I have tried using distinct but my problem seems
outside of it's capabilities.
The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
As you can see from this example, ID 1 and ID 14 are duplicated because
there are two tutors (TUT ID) for these courses. The entire rows are not
duplicated because of the differing TUT ID so distinct will not work (unless
I am using it wrong).
I need to have the output as is now, but show only a single instance of each
ID regardless of how many tutors the courses may have.
How can I achieve this?
ThanksKeith
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Keith" <@..> wrote in message news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi There
> I am having a problem with some SQL and hope someone out there can help.
I
> am fairly new so apologise any bad coding etc.
> I have created a View, which works fine except I need to eliminate some
> duplicate entries and I have tried using distinct but my problem seems
> outside of it's capabilities.
> The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
> The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
> As you can see from this example, ID 1 and ID 14 are duplicated because
> there are two tutors (TUT ID) for these courses. The entire rows are not
> duplicated because of the differing TUT ID so distinct will not work
(unless
> I am using it wrong).
> I need to have the output as is now, but show only a single instance of
each
> ID regardless of how many tutors the courses may have.
> How can I achieve this?
> Thanks
>|||Thanks
I don't want to list the duplicates though - I want to list everything
EXCEPT the duplicates.
I couldn't figure out (being a newbie) how to do this.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ON%23ucxWQEHA.236@.TK2MSFTNGP10.phx.gbl...
> Keith
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Keith" <@..> wrote in message
news:OU9c0rWQEHA.3916@.TK2MSFTNGP12.phx.gbl...
> > Hi There
> >
> > I am having a problem with some SQL and hope someone out there can help.
> I
> > am fairly new so apologise any bad coding etc.
> >
> > I have created a View, which works fine except I need to eliminate some
> > duplicate entries and I have tried using distinct but my problem seems
> > outside of it's capabilities.
> >
> > The SQL is here: http://www.step-online.org.uk/sql/Courses_SQL.png
> >
> > The Output is here: http://www.step-online.org.uk/sql/Courses_Output.png
> >
> > As you can see from this example, ID 1 and ID 14 are duplicated because
> > there are two tutors (TUT ID) for these courses. The entire rows are
not
> > duplicated because of the differing TUT ID so distinct will not work
> (unless
> > I am using it wrong).
> >
> > I need to have the output as is now, but show only a single instance of
> each
> > ID regardless of how many tutors the courses may have.
> >
> > How can I achieve this?
> >
> > Thanks
> >
> >
>|||Dear Keith
I have had a look at the data and have come to the
conclusion that it is not duplicated in any way.
The reason is that if you check each row in the output
they are all in some way unique. For instance id 14 (which
as in twice) has a different TUT ID.
To give you some sort of solution then we need to know
what do you actually want as un duplicated data.
Thanks
J
>--Original Message--
>Hi There
>I am having a problem with some SQL and hope someone out
there can help. I
>am fairly new so apologise any bad coding etc.
>I have created a View, which works fine except I need to
eliminate some
>duplicate entries and I have tried using distinct but my
problem seems
>outside of it's capabilities.
>The SQL is here: http://www.step-
online.org.uk/sql/Courses_SQL.png
>The Output is here: http://www.step-
online.org.uk/sql/Courses_Output.png
>As you can see from this example, ID 1 and ID 14 are
duplicated because
>there are two tutors (TUT ID) for these courses. The
entire rows are not
>duplicated because of the differing TUT ID so distinct
will not work (unless
>I am using it wrong).
>I need to have the output as is now, but show only a
single instance of each
>ID regardless of how many tutors the courses may have.
>How can I achieve this?
>Thanks
>
>.
>|||Hi and Thanks for looking
I know that the TUT ID is different.
I want to ignore this though and exclude all the records which are duplicate
(even if they have different TUT IDs).
TUT ID is the tutor assigned to the course, so a record is showing up twice
if there are two tutors on the course. I only want to show the record once,
regardless of how many tutors (TUT IDs) there are for each course.
For my purposes, a duplicate record is one where there is another record
where everything is identical EXCEPT the TUT ID, because there can be many
tutors on the same course.
Thanks
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:1160e01c4419c$2926d190$a101280a@.phx.gbl...
> Dear Keith
> I have had a look at the data and have come to the
> conclusion that it is not duplicated in any way.
> The reason is that if you check each row in the output
> they are all in some way unique. For instance id 14 (which
> as in twice) has a different TUT ID.
> To give you some sort of solution then we need to know
> what do you actually want as un duplicated data.
> Thanks
> J
>
> >--Original Message--
> >Hi There
> >
> >I am having a problem with some SQL and hope someone out
> there can help. I
> >am fairly new so apologise any bad coding etc.
> >
> >I have created a View, which works fine except I need to
> eliminate some
> >duplicate entries and I have tried using distinct but my
> problem seems
> >outside of it's capabilities.
> >
> >The SQL is here: http://www.step-
> online.org.uk/sql/Courses_SQL.png
> >
> >The Output is here: http://www.step-
> online.org.uk/sql/Courses_Output.png
> >
> >As you can see from this example, ID 1 and ID 14 are
> duplicated because
> >there are two tutors (TUT ID) for these courses. The
> entire rows are not
> >duplicated because of the differing TUT ID so distinct
> will not work (unless
> >I am using it wrong).
> >
> >I need to have the output as is now, but show only a
> single instance of each
> >ID regardless of how many tutors the courses may have.
> >
> >How can I achieve this?
> >
> >Thanks
> >
> >
> >.
> >|||Any suggestions would be great.
Thanks
"Keith" <@..> wrote in message
news:um6a%23eaQEHA.1620@.TK2MSFTNGP12.phx.gbl...
> Hi and Thanks for looking
> I know that the TUT ID is different.
> I want to ignore this though and exclude all the records which are
duplicate
> (even if they have different TUT IDs).
> TUT ID is the tutor assigned to the course, so a record is showing up
twice
> if there are two tutors on the course. I only want to show the record
once,
> regardless of how many tutors (TUT IDs) there are for each course.
> For my purposes, a duplicate record is one where there is another record
> where everything is identical EXCEPT the TUT ID, because there can be many
> tutors on the same course.
> Thanks
>
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:1160e01c4419c$2926d190$a101280a@.phx.gbl...
> > Dear Keith
> >
> > I have had a look at the data and have come to the
> > conclusion that it is not duplicated in any way.
> >
> > The reason is that if you check each row in the output
> > they are all in some way unique. For instance id 14 (which
> > as in twice) has a different TUT ID.
> >
> > To give you some sort of solution then we need to know
> > what do you actually want as un duplicated data.
> >
> > Thanks
> > J
> >
> >
> > >--Original Message--
> > >Hi There
> > >
> > >I am having a problem with some SQL and hope someone out
> > there can help. I
> > >am fairly new so apologise any bad coding etc.
> > >
> > >I have created a View, which works fine except I need to
> > eliminate some
> > >duplicate entries and I have tried using distinct but my
> > problem seems
> > >outside of it's capabilities.
> > >
> > >The SQL is here: http://www.step-
> > online.org.uk/sql/Courses_SQL.png
> > >
> > >The Output is here: http://www.step-
> > online.org.uk/sql/Courses_Output.png
> > >
> > >As you can see from this example, ID 1 and ID 14 are
> > duplicated because
> > >there are two tutors (TUT ID) for these courses. The
> > entire rows are not
> > >duplicated because of the differing TUT ID so distinct
> > will not work (unless
> > >I am using it wrong).
> > >
> > >I need to have the output as is now, but show only a
> > single instance of each
> > >ID regardless of how many tutors the courses may have.
> > >
> > >How can I achieve this?
> > >
> > >Thanks
> > >
> > >
> > >.
> > >
>