Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use an
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquettXe.asp?id=5006
John
"Rossco" wrote:

> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below also).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also
).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
--
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use a
n
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquett_e.asp?id=5006
John
"Rossco" wrote:

> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below al
so).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6)))
AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6)))
AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))sql

Query with sub-query still returns unexpected results

Hello All! Thanks in Advance.
Have the following query which continues to return duplicate results. Any
help would be appreciated I have also tried a not exists version (below also).
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
in (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))
--
SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
FROM ISAS.dbo.VW_class_dtls
WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
FROM last_sad.dbo.tblStudentOfferDetails1 where
(offer_type_code = 'M'))Hi
The rows are not duplicates! Each combination of columns will be distinct.
If you sort out which column is causing the multiple rows then you can use an
aggregate function (say MIN) on that column and group by the other columns.
When asking data specific questions posting DDL and Example data helps
considerably see http://www.aspfaq.com/etiquett­e.asp?id=5006
John
"Rossco" wrote:
> Hello All! Thanks in Advance.
> Have the following query which continues to return duplicate results. Any
> help would be appreciated I have also tried a not exists version (below also).
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) not
> in (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))
> --
> SELECT distinct RTRIM(CAST(STRM AS char(4)) + CAST(CLASS_NBR AS char(6))) AS
> off_cd, DESCR AS ds, descr as fs, ACAD_ORG AS ao, 'M', crse_code, location
> FROM ISAS.dbo.VW_class_dtls
> WHERE (STRM LIKE '%03') AND not exists (SELECT offer_code
> FROM last_sad.dbo.tblStudentOfferDetails1 where
> (offer_type_code = 'M'))

Query to update 1 record in a duplicate set of records

How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.

Without more info it's hard to tell, but you just need to qualify what you want to update:

UPDATE Table
SET column = 'New Value'
WHERE column = 'youNeedADateHere'

AND orderid = 'Whatever'
GO

|||Thanks for your response but I can't specify an orderid because there are 3612 records and within those 3612 records some of the id's are dups. The query above would do a specific orderid and a specific date. The dates are all different I just want the most recent per orderid. Thank you anyway.|||

Something along the lines of:

UPDATE Table1

SET NewCol = 1

FROM Table1 AS i

INNER JOIN (SELECT OrderID, COUNT(*) AS c, MAX(OrderDate) AS OrderDate

FROM Table1

GROUP BY OrderID

HAVING COUNT(*) > 1) AS Dupes

ON i.OrderID = Dupes.OrderID

AND i.OrderDate = Dupes.OrderDate

|||Thank you that is close enough to what I was looking for. I appreciate your response

Query to return duplicate records

I have a table with a column varchar(50), say colA.
How can I create a sql query that returns all records with duplicate colA ?
For example:
colA colB
1 A
2 B
2 B
3 C
2 is the duplicate records for colA. How can I return those records ?
Thanks.SELECT ColA, count(*) FROM TableName
GROUP BY ColA
HAVING COUNT(*) > 1
HTH. Ryan
"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>|||"Paul fpvt2" <Paulfpvt2@.discussions.microsoft.com> wrote in message
news:BF9FCDE6-61C4-4CD0-AEA7-98DE6049CE59@.microsoft.com...
>I have a table with a column varchar(50), say colA.
> How can I create a sql query that returns all records with duplicate colA
> ?
> For example:
> colA colB
> 1 A
> 2 B
> 2 B
> 3 C
> 2 is the duplicate records for colA. How can I return those records ?
> Thanks.
>
SELECT T.cola, T.colb
FROM your_table AS T
JOIN
(SELECT cola
FROM your_table
GROUP BY cola
HAVING COUNT(*)>1) AS D
ON T.cola = D.cola ;
David Portas
SQL Server MVP
--|||Select colA,ColB
>From Sometable
Where colA in
(
Select colA
From SomeTable
Group by colA
Having count(*) >1
)
HTH, jens Suessmeyer.

Query to merge duplicate records

Hello,

I have the following Query:

1 declare @.StartDatechar(8)
2 declare @.EndDatechar(8)
3 set @.StartDate ='20070601'
4 set @.EndDate ='20070630'
5 SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff])AS ProTime
6 FROM LogTableWHERE
7 [TimeOn]BETWEEN @.StartDateAND @.EndDateAND
8 [TimeOff]BETWEEN @.StartDateAND @.EndDate
9 ORDER BY [Position],[Initials]ASC

The query returns the following data:

Position Initials ProTime
---------------- --- ----
ACAD JJ 127
ACAD JJ 62
ACAD KK 230
ACAD KK 83
ACAD KK 127
ACAD TD 122
ACAD TJ 127

What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following:

Postition Initials ProTime

ACAD JJ 189
ACAD KK 440
ACAD TD 122
ACAD TJ 127

Any assistance greatly appreciated.

Use the GROUP BY syntax, and SUM() the time diffs.

Your query will look like this:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff])) AS ProTime
FROM LogTableWHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, Position

Leave off the ORDER BY from your statement - you see that the SQL is grouping by the columns you wish to merge and summing the variable data.

|||

Try:

SELECT Initials, [Position], SUM(DATEDIFF(mi,[TimeOn],[TimeOff]))AS ProTime
FROM LogTable

WHERE
[TimeOn]BETWEEN @.StartDateAND @.EndDateAND
[TimeOff]BETWEEN @.StartDateAND @.EndDate

GROUP BY Initials, [Position]
ORDER BY [Position],[Initials]ASC

|||

That was it! I knew it was going to be simple, but I could not "see the forest for the trees".

Thanks

Dan

Query to get Linked List kind of data from the Table

hi Experts,

I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of

ID

Duplicate_of

State

77637

65702

Duplicate

65702

42217

Duplicate

42217

-

Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.

Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.

/soni

This is a common problem, what you will find is that you actually have a tree structure where 42217 is the root of the tree, 65702 is a branch, and 77637 is a leaf. Have a search around Google for Celko's "nested set" which has a solution which should help you here.

Query to get Linked List kind of data from the Table

hi Experts,

I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of

ID

Duplicate_of

State

77637

65702

Duplicate

65702

42217

Duplicate

42217

-

Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.

Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.

/soni

Assuming you are using 2005 you can use a common table expression (CTE) and use the following syntax:

DECLARE @.ID int

SET @.ID = 77637

;WITH Dupes (ID, Duplicate_of, State) AS

(

SELECT

ID, Duplicate_of, State

FROM

dbo.test

WHERE

ID = @.ID

UNION ALL

SELECT

T.ID, T.Duplicate_of, T.State

FROM

dbo.test T

INNER JOIN Dupes D ON D.Duplicate_of = T.ID

)

SELECT *

FROM Dupes

|||

hi Weaver,

Many Thanks for looking at my problem.

i forgot to mention i use SQL Server 2000 as of now. cannot upgrade to SQL Server 2005. :(

/Soni

|||

You can use this function to return the root of the tree (assuming you wanted to do this one at a time :)

set nocount on
drop table issue
drop function issue$getRoot
go
create table issue
(
issueId int primary key,
duplicateOf int references issue(issueId)
)
insert into issue
select 1, NULL
insert into issue
select 2, 1
insert into issue
select 3, 2
insert into issue
select 4, NULL
insert into issue
select 5, 4
go
create function issue$getRoot
(
@.issueId int
)
returns int
as
begin
while(1=1)
begin
select @.issueId = issue.issueId
from issue
join issue as dup
on dup.duplicateOf = issue.issueId
where dup.issueId = @.issueId

if @.@.rowcount = 0
break
end
return @.issueId
end
go

select dbo.issue$getRoot (5)

|||

hi Louis

Thanks a million for yr expert logic. its working!!!. :)

/Soni

Query to filter duplicate data from table.

i am having 3000 records in table. now i want take out the duplicate from that table.
for example: i want to find out the duplicates of 'CompanyNames'.
help needed to write query for this operation.Originally posted by pln_verma
i am having 3000 records in table. now i want take out the duplicate from that table.

for example: i want to find out the duplicates of 'CompanyNames'.

help needed to write query for this operation.

check this :
http://www.sqlteam.com/item.asp?ItemID=3331|||Does your table have a surrogate key or any method of uniquely identifying a record?

Query Syntax Help

I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.

Thanks!

code:------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
------------------------what is the error you are getting?|||you have 4 tables, but what are the relationships and their cardinalities?

for example, Participant--PartReg is probably 1--m, but what about the others?

somewhere along the line you have two unrelated relationships giving you a cross join effect|||I am not getting an error, it is just pulling duplicate fields.

In the Participant table, ParticipantID is a Primary Key and in PartRace and PartReg tables it is a Foreign Key. In the RaceData table, RaceID is a Primary Key and in PartRace and PartReg it is a Foreign Key.

Hope this helps~|||Assuming your data is clean, I believe your selection list is misleading.
You are not pulling any colums of "rd" (RaceData) table.
Perhaps there is a 1-n realtion (participants has entered in more than one races ?). Selelect the PK of RaceData along, this may shed some light.


SELECT
p.ParticipantID,
pr.RaceID,
p.FirstName,
p.LastName,
pr.Bib,
p.Gender,
pr.Age,
pr.AgeGrp,
p.DOB,
p.Address,
p.City,
p.St,
p.Zip,
pr.Clyde,
pr.WhlChr,
pr.RcWlk,
p.Phone,
p.Email,
reg.ShrtSize,
reg.ShrtStyle,
reg.WhereReg,
reg.DateReg,
reg.AmtPd,
rd.RaceID
FROM Participant p
INNER JOIN
PartReg reg
ON p.ParticipantID = reg.ParticipantID
JOIN
PartRace pr
ON pr.ParticipantID = p.ParticipantID
JOIN
RaceData rd
ON pr.RaceID = rd.RaceID
WHERE
(
rd.EventID = 45
AND pr.RaceID = reg.RaceID
)
ORDER BY p.LastName|||Addendum: what is the relation/structure concerning "Eventid" / "RaceId" in RaceData ? does one RACE cover multiple EVENTS ?

You are joining via "EventId", but say PK is "RaceID" ?

...not seeing clear yet...|||Each event has one or more races. The EventID in Events is a primary key and in RaceData is a foreign key. The problem is that some people could have registered for multiple races hence they appear more than once in the PartRace and PartReg tables but only once in the Participant table. I am trying to identify all participants for all of the races in a specific event.|||I found the problem. It was an error in my code that was over-writing the raceid field with the most current one. The query was doing exactly what it was supposed to be doing. The data was corrupted but I have resolved it.

THANKS A TON FOR ALL OF YOUR HELP!