Duplicate Data in Table

Question

Post reply

Duplicate Data in Table

tsanders

Newbie

Points: 9
More actions
June 27, 2011 at 11:23 am

#145833

I'm trying to figure out why I'm getting duplicate data in my sql table. It should should be 262 rows but I'm getting 401. Here's my code: (The highlighted code is the part that I think is causing an issue. If you run this part by itself you'll see the duplication's)
drop proc MWLOpportunity_Site
go
create proc MWLOpportunity_Site @BidID nvarchar(30)
as
drop table [MWLWEB].dbo.[MWLSite_Temp]
create table [MWLWEB].dbo.[MWLSite_Temp]
([ID] nvarchar(255)
,[City] nvarchar(255)
,[State] nvarchar(255)
,[Type] nvarchar(255)
,[ShipperID] nvarchar(255)
,[MinCarrier] nvarchar(255)
,[MaxCarrier] nvarchar(255))
insert into [MWLWEB].dbo.[MWLSite_Temp] select [ToID] as [ID], [Origin City] as [City], [OriginST] as [State],
'Outbound' as [Type], [Client Name] as [ShipperID], '0' as [MinCarrier], '100' as [MaxCarrier] from MWLOpportunityView
union
select [FromID] as [ID], [Destination City] as [City], [DestinationST] as [State],
'Inbound' as [Type], [Client Name] as [ShipperID], '0' as [MinCarrier], '100' as [MaxCarrier] from MWLOpportunityView
where BidID = @BidID
and CarrSCAC = 'DMMY1'
drop table [MWLWEB].dbo.[MWLSite]
create table [MWLWEB].dbo.[MWLSite]
( [ID] nvarchar(255)
,[City] nvarchar(255)
,[State] nvarchar(255)
,[Type] nvarchar(255)
,[ShipperID] nvarchar(255)
,[MinCarrier] nvarchar(255)
,[MaxCarrier] nvarchar(255)
,[Y] nvarchar(255)
,[X] nvarchar(255))
insert into [MWLWEB].dbo.[MWLSite]( [ID]
,[City]
,[State]
,[Type]
,[ShipperID]
,[MinCarrier]
,[MaxCarrier]
,[Y]
,[X] )
select Tmp.*,
Coord.[Y],
Coord.[X]
from [MWLWEB].dbo.[MWLSite_Temp] Tmp
inner join [MWLWEB].[dbo].[MWL_XY_Coordinates] Coord
on Tmp.[City] = Coord.[City] and
Tmp.[State] = Coord.[State]
group by Tmp.[ID] ,
Tmp.[City],
Tmp.[State],
Tmp.[Type] ,
Tmp.[ShipperID],
Tmp.[MinCarrier] ,
Tmp.[MaxCarrier] ,
Coord.[Y],
Coord.[X]
[/color]
select * from [MWLWEB].dbo.[MWLSite_Temp]
go

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tsanders Newbie Points: 9 More actions · Answer 1

sry the code wasn't highlighted but it's the part bracketed by and [/color]

Lowell SSC Guru Points: 323755 More actions · Answer 2

you've got two SELECTS witha UNION, but only one of the selects have a WHERE clause...could that be it?

SELECT

[ToID] AS [ID],

[Origin City] AS [City],

[OriginST] AS [STATE],

'Outbound' AS [TYPE],

[Client Name] AS [ShipperID],

'0' AS [MinCarrier],

'100' AS [MaxCarrier]

FROM MWLOpportunityView

--Shouldn't there be a WHERE statement here?

--where BidID = @BidID

--and CarrSCAC = 'DMMY1'

UNION

SELECT

[FromID] AS [ID],

[Destination City] AS [City],

[DestinationST] AS [STATE],

'Inbound' AS [TYPE],

[Client Name] AS [ShipperID],

'0' AS [MinCarrier],

'100' AS [MaxCarrier] FROM MWLOpportunityView

WHERE BidID = @BidID

AND CarrSCAC = 'DMMY1'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323755 More actions · Answer 3

oh yeah, that inner join:

inner join [MWLWEB].[dbo].[MWL_XY_Coordinates] Coord

if that join has more than one record for any thing it's joining to, you could get mulitple rows....so if there are 4 points to create a rectangle of coords, youd get 4 rows for the matching item...that could be it also.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tsanders Newbie Points: 9 More actions · Answer 4

tsanders

Newbie

Points: 9

June 27, 2011 at 11:31 am

#1344699

thanks I'll see if that may be it