Bear with me.I have a group of tables across two dbs. All tables in both dbs have identical information, and same number of rows.In the MNI table, i must reduce each row to identify one person. Currently, there are multiple rows representing the same person. I have executed my scripts that bring the MNI table (in the MNI db) to the structure it needs to have. One row = one person. That table uses a column called MNINO to identify each person. There is a second column called ECSOID in that table which holds the same value as the MNINO for each row. In another db (Arrest), i have one table (Arr_per table) which needs to mirror the MNI table in the MNI db (one row for each person). Those rows will have a valued called ECSOID which will be the same ECSOID for the same person from the MNI table in the MNI db. The same scripts i used to reduce my MNI table will work to reduce the Arrest table. These rows will be identified by an Arrestno value but will have the ECSOID value which relates back to the MNI table in the MNI db.In that arrest db, i have other tables (Arrest and Arr_pc tables), which will have a "one to many" relationship between from the MNI table in the MNI db. The value in those two tables which needs to correspond to the MNI table is the ECSOID value. So that the ECSOID value for any and all entries regarding John Doe (Via his arrestno's) will be the single ECSOID value which exists for him in the MNI table back in the MNI db.So the issue is this. In the Arrest.dbo.arrest and arrest.dbo.pc tables, how do i set the ECSOID value to match the single ECSOID value for the same person back in the MNI.dbo.MNI table? table structures for Arrest.dbo.arrest and mni.dbo.mni are below:CREATE TABLE [dbo].[Arrest]( [BEGIN] [varchar](10) NULL, [ARRESTTYPE] [varchar](1) NULL, [WARSTATUS] [varchar](1) NULL, [OTTIC] [bit] NULL, [OFFENSENO] [varchar](15) NULL, [WARRANTNO] [varchar](15) NULL, [ARRESTNO] [varchar](15) NULL, [JAILBOOKNO] [varchar](15) NULL, [OBTS] [varchar](15) NULL, [OTHER] [varchar](15) NULL, [REPORTDATE] [datetime] NULL, [REPORTTIME] [varchar](5) NULL, [COURTCASE] [varchar](20) NULL, [LNAME] [varchar](20) NULL, [FNAME] [varchar](15) NULL, [MNAME] [varchar](15) NULL, [TITLE] [varchar](5) NULL, [DOB] [datetime] NULL, [APPROX_AGE] [varchar](5) NULL,
CREATE TABLE [dbo].[MNI]( [MNINO] [varchar](15) NULL, [ECSOID] [varchar](15) NULL, [CREATED] [datetime] NULL, [CREATET] [varchar](5) NULL, [CREATEDBY] [varchar](10) NULL, [LNAME] [varchar](20) NULL, [FNAME] [varchar](15) NULL, [MNAME] [varchar](15) NULL, [TITLE] [varchar](5) NULL, [DOB] [datetime] NULL, [APPROX_AGE] [varchar](5) NULL, [RACE] [varchar](1) NULL, [SEX] [varchar](1) NULL, [HISPANIC] [bit] NULL, [HEIGHT] [varchar](5) NULL, [WEIGHT] [smallint] NULL, [HAIR] [varchar](3) NULL, [EYES] [varchar](3) NULL, [SSN] [varchar](11) NULL, [IDNO] [varchar](25) NULL,
Complicated Inner join
Posted - 2012-06-19 : 14:07:01
