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
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
>
|||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...[vbcol=seagreen]
> I
not
> (unless
> each
>
Showing posts with label fairly. Show all posts
Showing posts with label fairly. Show all posts
Tuesday, March 27, 2012
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
>
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
>
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
> > >
> > >
> > >.
> > >
>
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
> > >
> > >
> > >.
> > >
>
Friday, February 24, 2012
DTS: Inserting the contents of a global variable into a table via a loop
Hello,
I am wondering if anyone can help me. I am fairly new to DTS and am having some difficulty getting something to work. I will explain below:
I have one table (table_a) which contains a listing of contact details. I want to copy the data from table_a to table_b. The tricky part is that I want to insert certain items into table_b depending on a conditional statement on the items in table_a. (ie. If value in table_a.column_a is 1 then insert "good" into table_b.column_a, etc)
I have done a lot of looking and found that an ActiveX Script Task is the answer. So I now have an Execute SQL Task that selects all the data from table_a into a global variable recordset, an ActiveX Script Task that loops through the recordset, determines the necessary values and modifies the SQL Statement of a temporary Execute SQL Task with the required INSERT statment (as explained in this article: http://www.databasejournal.com/features/mssql/article.php/1461581).
I run it and it all runs fine except that no data is inserted into the table!!! The SQL Statement in the temp Task does get modified with the INSERT statement and inserts the data fine when forced to run. But it appears that the temp Task is not being executed within the ActiveX Script.
Can anybody tell me how to force a step to run in this loop-based scenario, or if there is a much better way I could do this.
Thanks for any help. LeeIf I understand what you are trying to do...
Inputvalue = 10
if Inputvalue < 10, Output Value = 20, else Output Value = 30.
If you are trying to make those types of changes, then you can do some simple Transforms.
With two data sources, you can add a Transform Data Task.
You can modify (delete, then add) an activex transform within the Transform task that will allow you to modify outgoing data based on the data coming in.
If you need more help on this, see books online, ActiveX Script, then choose DTS.|||Thanks, yes. I was just looking into that as I received your post and it does indeed look like the ActiveX Transform option is the way to go.
I'm impressed you could decipher what I was after!
Thanks for the help.
I am wondering if anyone can help me. I am fairly new to DTS and am having some difficulty getting something to work. I will explain below:
I have one table (table_a) which contains a listing of contact details. I want to copy the data from table_a to table_b. The tricky part is that I want to insert certain items into table_b depending on a conditional statement on the items in table_a. (ie. If value in table_a.column_a is 1 then insert "good" into table_b.column_a, etc)
I have done a lot of looking and found that an ActiveX Script Task is the answer. So I now have an Execute SQL Task that selects all the data from table_a into a global variable recordset, an ActiveX Script Task that loops through the recordset, determines the necessary values and modifies the SQL Statement of a temporary Execute SQL Task with the required INSERT statment (as explained in this article: http://www.databasejournal.com/features/mssql/article.php/1461581).
I run it and it all runs fine except that no data is inserted into the table!!! The SQL Statement in the temp Task does get modified with the INSERT statement and inserts the data fine when forced to run. But it appears that the temp Task is not being executed within the ActiveX Script.
Can anybody tell me how to force a step to run in this loop-based scenario, or if there is a much better way I could do this.
Thanks for any help. LeeIf I understand what you are trying to do...
Inputvalue = 10
if Inputvalue < 10, Output Value = 20, else Output Value = 30.
If you are trying to make those types of changes, then you can do some simple Transforms.
With two data sources, you can add a Transform Data Task.
You can modify (delete, then add) an activex transform within the Transform task that will allow you to modify outgoing data based on the data coming in.
If you need more help on this, see books online, ActiveX Script, then choose DTS.|||Thanks, yes. I was just looking into that as I received your post and it does indeed look like the ActiveX Transform option is the way to go.
I'm impressed you could decipher what I was after!
Thanks for the help.
Subscribe to:
Posts (Atom)