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...
> I
not[vbcol=seagreen]
> (unless
> each
>