Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Eliminating Duplicates

    Have a pretty simple wuestion but the answer seems to be evading me:

    Here's the DDL for the tables in question:

    Code:
    CREATE TABLE [dbo].[Office] (
    	[OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
    	[ParentOfficeID] [int] NOT NULL ,
    	[WebSiteID] [int] NOT NULL ,
    	[IsDisplayOnWeb] [bit] NOT NULL ,
    	[IsDisplayOnAdmin] [bit] NOT NULL ,
    	[OfficeStatus] [char] (1)  NOT NULL ,
    	[DisplayORD] [smallint] NOT NULL ,
    	[OfficeTYPE] [varchar] (10)  NOT NULL ,
    	[OfficeNM] [varchar] (50)  NOT NULL ,
    	[OfficeDisplayNM] [varchar] (50)  NOT NULL ,
    	[OfficeADDR1] [varchar] (50)  NOT NULL ,
    	[OfficeADDR2] [varchar] (50)  NOT NULL ,
    	[OfficeCityNM] [varchar] (50)  NOT NULL ,
    	[OfficeStateCD] [char] (2)  NOT NULL ,
    	[OfficePostalCD] [varchar] (15)  NOT NULL ,
    	[OfficeIMG] [varchar] (100)  NOT NULL ,
    	[OfficeIMGPath] [varchar] (100)  NOT NULL ,
    	[RegionID] [int] NOT NULL ,
    	[OfficeTourURL] [varchar] (255)  NULL ,
    	[GeoAreaID] [int] NOT NULL ,
    	[CreateDT] [datetime] NOT NULL ,
    	[UpdateDT] [datetime] NOT NULL ,
    	[CreateByID] [varchar] (50)  NOT NULL ,
    	[UpdateByID] [varchar] (50)  NOT NULL ,
    	[OfficeBrandedURL] [varchar] (255)  NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[OfficeManagement] (
    	[OfficeID] [int] NOT NULL ,
    	[PersonnelID] [int] NOT NULL ,
    	[JobTitleID] [int] NOT NULL ,
    	[CreateDT] [datetime] NOT NULL ,
    	[CreateByID] [varchar] (50)  NOT NULL ,
    	[SeqNBR] [int] NOT NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[OfficeMls] (
    	[OfficeID] [int] NOT NULL ,
    	[SourceID] [int] NOT NULL ,
    	[OfficeMlsNBR] [varchar] (20)  NOT NULL ,
    	[CreateDT] [datetime] NOT NULL ,
    	[UpdateDT] [datetime] NOT NULL ,
    	[CreateByID] [varchar] (50)  NOT NULL ,
    	[UpdateByID] [varchar] (50)  NOT NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[Personnel] (
    	[PersonnelID] [int] IDENTITY (1, 1) NOT NULL ,
    	[PersonnelDisplayName] [varchar] (100)  NOT NULL ,
    	[FirstNM] [varchar] (50)  NOT NULL ,
    	[PreferredFirstNM] [varchar] (50)  NOT NULL ,
    	[MiddleNM] [varchar] (50)  NOT NULL ,
    	[LastNM] [varchar] (50)  NOT NULL ,
    	[PersonalTaxID] [varchar] (9)  NOT NULL ,
    	[HireDT] [datetime] NOT NULL ,
    	[TermDT] [datetime] NOT NULL ,
    	[HomePhoneNBR] [varchar] (15)  NULL ,
    	[HomeADDR1] [varchar] (50)  NOT NULL ,
    	[HomeADDR2] [varchar] (50)  NOT NULL ,
    	[HomeCityNM] [varchar] (50)  NOT NULL ,
    	[HomeStateCD] [char] (2)  NOT NULL ,
    	[HomePostalCD] [varchar] (15)  NOT NULL ,
    	[PersonnelLangCSV] [varchar] (500)  NOT NULL ,
    	[PersonnelSlogan] [varchar] (500)  NOT NULL ,
    	[BGColor] [varchar] (50)  NOT NULL ,
    	[IsEAgent] [bit] NOT NULL ,
    	[IsArchAgent] [bit] NOT NULL ,
    	[IsOptOut] [bit] NOT NULL ,
    	[IsDispOnlyPrefFirstNM] [bit] NOT NULL ,
    	[IsHideMyListingLink] [bit] NOT NULL ,
    	[IsPreviewsSpecialist] [bit] NOT NULL ,
    	[AudioFileNM] [varchar] (100)  NULL ,
    	[iProviderID] [int] NOT NULL ,
    	[DRENumber] [varchar] (10)  NOT NULL ,
    	[AgentBrandedURL] [varchar] (255)  NOT NULL ,
    	[CreateDT] [datetime] NOT NULL ,
    	[UpdateDT] [datetime] NOT NULL ,
    	[CreateByID] [varchar] (50)  NOT NULL ,
    	[UpdateByID] [varchar] (50)  NOT NULL ,
    	[IsDisplayAwards] [bit] NOT NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[PersonnelMLS] (
    	[PersonnelID] [int] NOT NULL ,
    	[SourceID] [int] NOT NULL ,
    	[AgentMlsNBR] [varchar] (20)  NOT NULL ,
    	[CreateDT] [datetime] NOT NULL ,
    	[UpdateDT] [datetime] NOT NULL ,
    	[CreateByID] [varchar] (50)  NOT NULL ,
    	[UpdateByID] [varchar] (50)  NOT NULL 
    ) ON [PRIMARY]
    GO
    
    
    ALTER TABLE [dbo].[Office] ADD 
    	CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY 
    	(
    		[OfficeID]
    	) REFERENCES [dbo].[OfficeProfile] (
    		[OfficeID]
    	) NOT FOR REPLICATION 
    GO
    
    alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile]
    GO
    
    ALTER TABLE [dbo].[OfficeManagement] ADD 
    	CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY 
    	(
    		[JobTitleID]
    	) REFERENCES [dbo].[LookupJobTitle] (
    		[JobTitleID]
    	),
    	CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY 
    	(
    		[OfficeID]
    	) REFERENCES [dbo].[Office] (
    		[OfficeID]
    	) NOT FOR REPLICATION ,
    	CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY 
    	(
    		[PersonnelID]
    	) REFERENCES [dbo].[Personnel] (
    		[PersonnelID]
    	) ON DELETE CASCADE 
    GO
    
    alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office]
    GO
    
    ALTER TABLE [dbo].[OfficeMls] ADD 
    	CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY 
    	(
    		[OfficeID]
    	) REFERENCES [dbo].[Office] (
    		[OfficeID]
    	) NOT FOR REPLICATION 
    GO
    
    alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office]
    GO
    
    ALTER TABLE [dbo].[PersonnelMLS] ADD 
    	CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY 
    	(
    		[PersonnelID]
    	) REFERENCES [dbo].[Personnel] (
    		[PersonnelID]
    	) NOT FOR REPLICATION 
    GO
    
    alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel]
    GO

    Here's the query I'm having trouble with:
    Code:
    SELECT distinct Personnel.PersonnelID, 
               Personnel.FirstNM,    
               Personnel.LastNM, 
               Office.OfficeNM, 
               Office.OfficeID, 
               OfficeMls.SourceID AS OfficeBoard, 
               PersonnelMLS.SourceID AS AgentBoard
    FROM Personnel INNER JOIN
            OfficeManagement ON 
            Personnel.PersonnelID = OfficeManagement.PersonnelID 
    INNER JOIN
           Office ON OfficeManagement.OfficeID = Office.OfficeID 
    INNER JOIN 
           OfficeMls ON Office.OfficeID = OfficeMls.OfficeID 
    INNER JOIN
           PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID
    where officemls.sourceid  <> personnelmls.sourceid 
    and office.officenm not like ('%admin%')
    group by PersonnelMLS.SourceID, 
    Personnel.PersonnelID, 
    Personnel.FirstNM, 
    Personnel.LastNM, 
    Office.OfficeNM, 
    Office.OfficeID,  
    OfficeMls.SourceID
    order by office.officenm
    What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:

    Code:
    PersonnelID FirstNM                                            LastNM                                             OfficeNM                                           OfficeID    OfficeBoard AgentBoard  
    ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- 
    18205       Margaret Peggy                                     Quattro                                            Aventura North                                     650         906         908
    18205       Margaret Peggy                                     Quattro                                            Aventura North                                     650         918         908
    15503       Susan                                              Jordan                                             Blackburn Point                                    889         920         909
    15503       Susan                                              Jordan                                             Blackburn Point                                    889         921         909
    15503       Susan                                              Jordan                                             Blackburn Point                                    889         921         920
    15279       Sandra                                             Humphrey                                           Boca Beach North                                   890         917         906
    15279       Sandra                                             Humphrey                                           Boca Beach North                                   890         906         917
    15279       Sandra                                             Humphrey                                           Boca Beaches                                       626         917         906
    15279       Sandra                                             Humphrey                                           Boca Beaches                                       626         906         917
    13532       Michael                                            Demcho                                             Boca Downtown                                      735         906         917
    14133       Maria                                              Ford                                               Boca Downtown                                      735         906         917
    19126       Michael                                            Silverman                                          Boca Glades Road                                   736         917         906
    18920       Beth                                               Schwartz                                           Boca Glades Road                                   736         906         917
    If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.

    I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.

    Any help would be greatly appreciated.

    Thanks in advance!
    Last edited by ansonee; 02-11-05 at 14:07.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Could you edit the post and wrap [ code] [ /code ] tags around it?

    Just reomve the spaces in the tags
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    You're right...that is a LOT better!! Thanks for the tip...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do the same with the data...

    Why are you doing a GROUP BY? To get DISTINCT data?

    EDIT: And can you post the DDL for the tables
    Last edited by Brett Kaiser; 02-11-05 at 13:47.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    ...and I guess I really don't need the GROUP BY....
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    As far as i can see .. the rows are not exactly duplicates ... OfficeBoard id seems to be differing ... a joi n problem .. maybe ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    didnt read the post completely .. my bad !!!

    Didnt get what exactly you were trying to accomplish ....

    How do you decide that Sandra Humphries is outta office 626 ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Office 626 is the office she's associated with - it's set up in the main table. Been working on the join and can't seem to work out the bugs on this one...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  9. #9
    Join Date
    Feb 2005
    Posts
    78
    you could try replacing
    "where officemls.sourceid <> personnelmls.sourceid"
    with
    "where personnelmls.sourceid not in
    (select sourceid from officemls omls join office o on omls.officeid = o.officeid
    where officemanagement.officeid = omls.officeid)"
    This will give you duplicates because of the "distinct" but only the wrong personnelmls.sourceid should be selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •