duplicates in resultset
-
don't mind me david - was just having some fun before I sally forth into my internal office and become a workable deptworker...
where remi is involved, I usually let him - in his unique, kind and gentle way - extract all required information before breaking into a sweat over any t-sql problems...
**ASCII stupid question, get a stupid ANSI !!!** -
That's why I ask for that much inf... I don't need to sweat long because I hav everything I need to make it work
. -
you forgot to say - "in my unique, kind and gentle way"..
**ASCII stupid question, get a stupid ANSI !!!** -
Besides,
I always like to wait for our Lord and Master, Supreme Being to produce the light for us to see forth into the wilderness, hail Remi
Sycophants rule... but only if their masters allow them to
Far away is close at hand in the images of elsewhere.
Anon. -
And, sushila....
why is it everytime you join a thread it goes awol and off the beaten track
Far away is close at hand in the images of elsewhere.
Anon. -
Hi guys, if I generate sample data with that stored procedure that RGR provided the link for, will it screw up the data in my DB?
I was going to use this:
EXEC sp_generate_inserts 'table_name'
-
No.
-
Thanks RGR!
-
I ran that insert SP and this is all I get back...am I missing something here?
INSERT INTO [attorneygroup] ([AGid],[AGgroupName],[AGcomments],[AGenteredBy],[AGenteredDate],[AGmodifiedBy],[AGmodifiedDate])VALUES('AG0','Sample Attorney Group','Sample Comment','Admin','Oct 9 1999 12:00:00:000AM','Admin','Oct 9 1999 12:00:00:000AM')
-
Yup, rows for every other tables in the join and the results that the query should give WITH THOSE ROWS.
-
So I run that insert SP on every table in my query, then just cut & paste the results of the SP here?
-
Yup, WITH THE REQUIRED RESULTS.
Last time I say that to you.
-
One more time? PLEASE
just kidding!
-
INSERT INTO [users] ([Uid],[UinternalOfficeID],[UworkgroupTypeID],[Uname],[UpID],[UnameFirst],[UnameLast],[Utitle],[Uphone],[Ufax],[Uemail],[UcaseManager],[UenteredBy],[UenteredDate],[UmodifiedBy],[UmodifiedDate],[Upassword],[Upermissions],[Uhash],[ULocation])VALUES('US72','IO3','TRUTXXX','ejohnson','ejoh','Endia','Johnson','Case Worker',NULL,NULL,'ejohnson@litsol.com','kmccollum','junkman','Jan 26 2004 12:00:00:000AM','junkman','Apr 5 2004 1:33:51:000PM','TerMinateD','USR','81153238378496524708842909949','P')
INSERT INTO [workrequest] ([WRid],[WRreceivedDate],[WRattorneyFileID],[WRdateOfAccident],[WRrequestSubjectID],[WRinsuredName],[WRrequestGeneratorID],[WRfileContactID],[WRbillToID],[WRbillToFileID],[WRlegalCaseID],[WRcorrespTo],[WRenteredBy],[WRenteredDate],[WRmodifiedBy],[WRmodifiedDate],[WRcaseWorker],[WRcaseManager],[WRarchivedBy],[WRarchivedDate],[WRinternalOfficeID])VALUES('WR25310','Jul 6 2005 9:33:07:000AM','12541AL/RSO','May 25 1997 12:00:00:000AM','RS8799','','RG18528','RG3842','RG627','010 950 142 493','',1,'jjohnson','Jul 6 2005 9:33:07:000AM','jjohnson','Jul 6 2005 9:48:55:000AM','jjohnson','smacik',NULL,NULL,'IO0')
INSERT INTO [providerlink] ([PLid],[PLworkRequestID],[PLmedicalProviderID],[PLrecordTypeID],[PLrushTypeID],[PLrecordInstructions],[PLcomments],[PLcaseWorker],[PLattentionLine],[PLcurrentStatus],[PLstatusUpdated],[PLenteredBy],[PLenteredDate],[PLmodifiedBy],[PLmodifiedDate],[PLdueDate],[PLtat],[PLunworkable],[PLbillingExceptions])VALUES('PL148496','WR25686','MP26046','RTM','TRRT30D','PLEASE RUSH! DUE PRIOR TO 9/9/05!!: a complete copy of any and all medical records from 11/19/1963 to present, including records, charts, test results, reports, correspondence, office notes, and computerized records.','','','Medical Records Correspondence',90,'Aug 26 2005 1:14:40:000PM','jjohnson','Aug 10 2005 12:51:06:000PM','jjohnson','Aug 29 2005 8:17:36:000AM','Sep 9 2005 12:54:17:000PM',14,0,' ')
INSERT INTO [internaloffice] ([IOid],[IOofficeName],[IOofficeAbbrev],[IOcontactFirstName],[IOcontactLastName],[IOaddress1],[IOaddress2],[IOcity],[IOstate],[IOzip],[IOzip4],[IOphone],[IOfax],[IOmas90locationID],[IOdivision],[IOlocation])VALUES('IO0','smithfield','W','Christie','Grisetti','Brentwood Towne Centre','101 Towne Square Way, Suite 251','smithfield','PA','15227',NULL,'412.263.5656','412.882.3477','41000-03-1','03','10')
INSERT INTO [billmaster] ([BMid],[BMtransactionDate],[BMworkRequestID],[BMbillableEntityID],[BMtransactionType],[BMtransactionAmount],[BMadditionalInfo],[BMtransactionStatus],[BMdeletedDate],[BMinvoiceNumber],[BMinvoiceDate],[BMcaseWorker],[BMenteredBy],[BMenteredDate],[BMmodifiedBy],[BMmodifiedDate])VALUES('BM132313','Jul 26 2005 11:27:27:000AM','WR25031','PL141910','TRTTSTM',5.250000000000000e+001,NULL,NULL,NULL,'R132825','Aug 1 2005 8:40:32:000AM','neterovich','neterovich','Jul 26 2005 11:27:27:000AM',NULL,NULL)
cgrisetti cgrisetti smithfield 0 0 7 70.847142857142856 495.92999999999995 NULL 0.36842105263157893 0 0
dbabic spolto smithfield 0 0 0 NULL NULL NULL NULL 2 0
rmason aopnbrier smithfield NULL NULL 0 NULL NULL NULL NULL 0 0
rmason cpul mayview 0 0 0 NULL NULL NULL NULL 0 0
rmason rmaon smithfield 105 106 88 73.603181818181824 6477.0799999999999 3.0 4.6315789473684212 42 1
rmason scanini smithfield NULL NULL 0 NULL NULL NULL NULL 0 0
rmason tbroks smithfield NULL NULL 0 NULL NULL NULL NULL 0 0
rmason wseried smithfield NULL NULL 0 NULL NULL NULL NULL 0 0
smacik erasey smithfield NULL NULL 0 NULL NULL NULL NULL 0 0
smacik hsith smithfield 280 64 259 85.28208494208495 22088.060000000001 17.0 13.631578947368421 253 10
smacik jjhson smithfield 315 186 315 95.054514285714276 29942.171999999995 21.0 16.578947368421051 398 5
smacik kaubin smithfield 295 91 246 90.992113821138176 22384.05999999999 14.0 12.947368421052632 282 0
smacik lhardig smithfield 246 105 224 113.37227678571426 25395.389999999996 15.0 11.789473684210526 236 3
smacik mldtsko smithfield 230 135 204 73.044803921568644 14901.140000000003 11.0 10.736842105263158 251 41
smacik netevich smithfield 289 187 295 102.20257627118646 30149.760000000006 19.0 15.526315789473685 324 14
smacik nloer smithfield 311 180 277 75.812743682310526 21000.130000000016 19.0 14.578947368421053 393 6
smacik sluen smithfield 384 85 315 105.92650793650797 33366.850000000013 21.0 16.578947368421051 280 1
smacik smik smithfield 55 74 63 113.18936507936512 7130.9300000000021 3.0 3.3157894736842106 16 0
-
I have eliminated everything that is not relevant to simplify the problem. See the tables and data and the end of this message.
Based on the generated date, the expected result should have 4 rows since there are 2 users, 2 offices and all 4 cobminations of users and offices have at least one row in table webrequest.
The simplified SQL returns 12 rows based on the generated data.
SELECT UdeptManager, Uname, IOofficeName AS Office
FROM users
LEFT JOIN webrequest ON webrequest.WRdeptWorker = users.Uname
RIGHT JOIN internaloffice ON internaloffice.IOid= webrequest.WRinternalOfficeId
WHERE Uname IS NOT NULL -- Why is this needed when the column is not null
ORDER BY UdeptManager, Uname
If you expect only one row for each user and office, then:
1.Column IOofficeName in table internaloffice must be unique. In the below example data, 'The White House' is the only value for office name which results in what appear to be duplicates but are not.
2.Since each user could have multiple webrequest rows that could be for the same offices, duplicates need to be eliminated in the FROM clause not by using a DISTINCT in the outer select.
This SQL returns 2 rows, has no duplicate BUT IS STILL WRONG because IOofficeName is not unique
SELECT users.UdeptManager, users.Uname, WorkItems.IOofficeName AS Office
FROM users
LEFT JOIN (select distinct webrequest.WRdeptWorker , internaloffice.IOofficeName
From webrequest
RIGHT JOIN internaloffice
ON internaloffice.IOid= webrequest.WRinternalOfficeId
) as WorkItems (Uname, IOofficeName)
on WorkItems.Uname = Users.Uname
ORDER BY users.UdeptManager, users.Uname
This is the correct solution and returns 4 rows:
SELECT users.UdeptManager, users.Uname , WorkItems.IOofficeName AS Office, WorkItems.IOid
FROM users
LEFT JOIN (select distinct webrequest.WRdeptWorker , internaloffice.IOid , internaloffice.IOofficeName
From webrequest
RIGHT JOIN internaloffice
ON internaloffice.IOid= webrequest.WRinternalOfficeId
) as WorkItems (Uname, IOid , IOofficeName)
on WorkItems.Uname = Users.Uname
ORDER BY users.UdeptManager, users.Uname
Schema and test data follows:
CREATE TABLE users
( Uname varchar (32) NOT NULL
, UdeptManager varchar (32) NULL
, CONSTRAINT PK_users PRIMARY KEY CLUSTERED (Uname)
)
GO
CREATE TABLE webrequest
( WRid varchar (16) NOT NULL
, WRdeptWorker varchar (30) NULL
, WRinternalOfficeID varchar (10) NULL
, CONSTRAINT PK_webrequest PRIMARY KEY CLUSTERED ( WRid )
)
GO
CREATE TABLE internaloffice
( IOid varchar (16) NOT NULL
, IOofficeName varchar (32) NULL
CONSTRAINT PK_internaloffice PRIMARY KEY CLUSTERED (IOid)
)
GO
Insert into Users
( Uname , UdeptManager)
select 'u1','CFEDERL' union all
select 'u2','CFEDERL'
go
INSERT INTO internaloffice
( IOid ,IOofficeName )
select 'A', 'The White House' union all
select 'B', 'The White House'
go
Insert into webrequest
( WRid , WRdeptWorker, WRinternalOfficeID )
select Users.Uname + '!' + internaloffice.IOid + '!' + Sequences.SEQ
, Users.Uname, internaloffice.IOid
from USers
CROSS JOIN internaloffice
CROSS JOIN(select '1' union all select '2' union all select '3') as Sequences (SEQ)
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply