Link to home
Start Free TrialLog in
Avatar of jriver12
jriver12

asked on

need to join

Ok!, been at it for a while now with out any sleep (36 hours) and I can't think straight any more, So I as you for a salution.

(inherited problem)

I have multiple DB (sql2K)
each built independently to the other.

the first.
name expo
the pk/fk for all recods is "exid" within its tables

the seconds member PK/FK is "MID" within its tables

now I must join these record into a db contacts (PK/FK) will be "CID"

this table will now become the parent talbe for all dbs.

hence the CID will be the FK constraint that will assign all other records
therefore

cid will have a one to many relationship with expo and with member

I have already place all records from member and expo, I have made sure that I do not have any duplicate record.  now all these records now have a new id "CID" now I want to propagate this CID down to all other tables ExPO and Member. I have created a new column in both tables to rep contact id.

now what I have tried and failed due to sleep is to take the cid and addit to both expo and member table where the following criteria is met( this creteria will be deleted out of child tables aftere testing) conatact address,city,state,zip,email are the same for both member and expo.

this is what I have I am stuck on the update portion(YaaaaawWWWnnn)

I know it will be simple. just to tired to proceed with thought.
I am going to get a coffee and a smoke and try to revive.


<cfquery name="getex" datasource="uspoultryintranet">
Select * from currentex
</cfquery>


<cfoutput query="getex">
<cfquery name="getcn" datasource="uspoultryintranet">
Select contactid from curcontacts
where
(lname='#Getex.lname#')and
(fname='#Getex.fname#')and
(address1='#Getex.address1#')and
(city='#Getex.city#')and
(State='#Getex.State#')and
(email='#Getex.email#')
</cfquery>
</cfoutput>

<!--- update expo table with new CID goes here--->



Avatar of anandkp
anandkp
Flag of India image

wld this help ?

<CFQUERY NAME="getcn" DATASOURCE="uspoultryintranet">
      Select cc.contactid, ce.contactid, ce.lname, ce.fname, ce.address1, ce.State, ce.email
      from curcontacts cc, currentex ce
      where cc.contactid = ce.contactid
      Order by ce.lname, ce.fname
      <!--- U can have joins on all of the above values - but i assume there wld be a common ContactId b/w the 2 tables --->
</CFQUERY>
how about this

i have assumed that uspoultryintranet dsn is for the parent cid table
and i have used memberdsn for member table and expodsn for expo table

<cfquery name="getex" datasource="uspoultryintranet">
      Select * from currentex
</cfquery>

<cfoutput query="getex">
      
      <CFSET sLName = Getex.lname>
      <CFSET sFName = Getex.fname>
      <CFSET sAddress = Getex.address1>
      <CFSET sCity = Getex.city>
      <CFSET sState = Getex.State>
      <CFSET sEmail = Getex.email>
      
      <cfquery name="getcn" datasource="uspoultryintranet">
            Select       contactid from curcontacts
            where       lname='#sLName#'
                           and fname='#sFName#'
                        and address1='#sAddress#'
                        and city='#sCity#'
                        and State='#sState#'
                        and email='#sEmail#'
      </cfquery>
      
      <CFLOOP QUERY="getcn">
            <CFQUERY NAME="Upd_Member" DATASOURCE="MemberDsn">
                Update       member
                  set       contactid = #contactid#
                  where       address1='#sAddress#'
                              and city='#sCity#'
                              and State='#sState#'
                              and email='#sEmail#'
          </CFQUERY>
            
            <CFQUERY NAME="Upd_Expo" DATASOURCE="ExpoDsn">
                Update       expo
                  set       contactid = #contactid#
                  where       address1='#sAddress#'
                              and city='#sCity#'
                              and State='#sState#'
                              and email='#sEmail#'
          </CFQUERY>
      </CFLOOP>
            
</cfoutput>


Regards
Hart
could we get some more background?

Will these old tables with pk mid and exid continue to live beyond this project?

What is your end goal of linking CID to those tables?

Wouldn't it be easier to have a lookup table in your new DB Schema

CID   MID   EXID

links all three.  Then you can import your older tables to your new DB schema and enforce referential integrity.

CJ
Avatar of jriver12
jriver12

ASKER

CJ, Where you been?

Lil more Back ground. (hold on for the ride)

these DB have been inherited.

Membership
this table has a pk of mid(1,1)
all data within this is address (contact info)

Expo.
in house devd appl db.
has a pk of Exid (1,1)

each holding about 12000 to 25000 records each.

now the pres whant to create a parent (table) name it  Contact, this db table will initially create the primary record (contact information) and assign it a pk (1,1) let call it CID.

now the CID will now become the FK to the two above mentioned db(tables).

now in this process I must also create the procedure that will take * records from membership and expo populate the contact table with those recordsets and ensure that no duplicate records exist in contacts.

I hope that this is enough backgroud for you.

working onthis end too

thanks
:-) been busy with work

the common data linking the two legacy DBs is what?

CJ
the two legacies where never linked, now I want the CID to be the linking Factor.
let me rephrase, how are you planning on linking them.. using what common data point?

CJ
i hd planned to add a colunm to both the membership and expo tables named CID, and have that CID be the FK constraint linking all data back to the contacts table.
ok, but how do you know which row in the exp table maps to what row in the membership table.

If you want to link the two tables you need to have an indentifying data point.  Some column/field that is common to both or are you arbitrarily creating relationships?

CJ
AHH I see said the blind man.

well this is the process that I am trying to contruct.

1. being that the contact table is new and the CID column will be the pK for all now, I must populate it with the records from member and exp.  ensuring that there are no Duplicates added during the process. the criteria to find the duplicates will be  a combination of email,address1, fname, and lname if all these match then it is a dup.

now once the conatct table has been populated, then I must now run a second check

what i think is.
get * cid and email from conatact.
get all records from expo where email is = to conatct.email
update the new cid column in expo with the matching contact.cid

Visa versa wdt member?

hence now the conatact.cid has a one to many with expo.cid and member.cid.

once this is confirmed  and tested I can go ahead and delete the redundant data that lives in all tables hence normalization.


How's my theory???
:^|
Before I respond, quick question - is email always unique? Meaning Two people can't have the same email address right? -  I have found email is a good data point rather than a combination of email, last_name and first_name.

do a count distinct email_address from each table.  See if it comes to the number of total rows in each schema and that could be your common data point.

CJ
I miss spoke,
I did not mean a combination of email,last_name, and First_name.
what I meant was just email as the pointer.

although now that I think about it you bought up a good point  two people can have the same address so maybe it should be better to identify the records with a combo of fname,lname, and email.
ok, well how about this.  See if you can run a query like select distinct email_address from expo
and select distinct email_address from membership.  See if you get the same number of results and the number of results equals the number of rows in each table.  If that is the case you dont' have dup emails.

If you don't have dup emails then your process is simpler

Your process will have to be multi stepped and use temp tables or run once but store the temp data in vars/memory.

Select * from expo
Select * from membership

Now you have two queries with all the data.

Insert each query into structures keying of a lowercased email address.

Now loop through one structure while finding the email address key in the second structure.  Get the values of the exid and mid and store them in a new structure that holds these together.

Now you can create your CID mapping.

This maybe over complicated but the basics are there.

or you can create a temp table called cid_mapping where you do this:

select lower(email_address), exid from expo

loop over expo query
select mid from membership where lower(email_address) = exp.email_address

get your corresponding cid and then

insert into cid_mapping
values (cid, mempership.mid, exp.exid)

end loop

Just thoughts.

CJ
will test and let you know.
k, headed out to dinner soon, will check for updates in a couple of hours or so.

CJ
did u event look at my post ???
did u event look at my post ???
hart, I have ad it works, after testing I have discovered that it would put duplicates into the contacts table.  
I am trying to alter so It will not add a duplicate record based onthe fname,lname,email into the contacts table, however still place the cid in to the corresponding records.

the flow I am trying to achieve is the following.

insert all records from the member and expo tables into the contacts table.

1. insert member first.
2. insert expo
3. if the record from expo matches criteria in the contact (that has been populated by member)then assign that CID to the expo.cid column.
4. if no match was found in the contact table from expo then insert that record into the contacts table.  assign it a new cid and populate the expo table with the new cid that has now been assigned in the contacts table.

your post does all but check for the matching record before insertion.

any suggestions?

thanks
Another approach:

Create a table that has all the following columns dumped in it from the one of the tables, lets say expo

email_address
first_name
last_name
exid

design the table so it has an autogenerated cid and also has a mid column.

Now you will have a cid generated for each unique email_address, first_name and last_name from one table and the corresponding exid.

now you need to go through the second table and fine the matching mid and update your new table with the mid
something like
query1:
select fname, lname, email_address, cid from newcidtable

<cfloop over query1>
query2:
select mid
from membership where
fname = '#query1.fname#'
and lname = '#query1.lname#'
and email_address = '#query1.email_address#'

<cfif query2.recordcount eq 1>
<!--- it exists so update --->
  update newcidtable set mid = #query2.mid# where cid = #query1.cid#
<cfelse>
<!--- it did not exist so insert which means it will not have a corresponding exid - this probably shouldn't happen--->
  insert into newcidtable
</cfif>

</cfloop>

CJ
ASKER CERTIFIED SOLUTION
Avatar of cheekycj
cheekycj
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
not yet I havent worked on it since the morn I have encountered 2 other fires (down servers).

I will let you know.
"I have already place all records from member and expo, I have made sure that I do not have any duplicate record.  now all these records now have a new id "CID" now I want to propagate this CID down to all other tables ExPO and Member. I have created a new column in both tables to rep contact id.

now what I have tried and failed due to sleep is to take the cid and addit to both expo and member table where the following criteria is met( this creteria will be deleted out of child tables aftere testing) conatact address,city,state,zip,email are the same for both member and expo.

this is what I have I am stuck on the update portion(YaaaaawWWWnnn)"...

the above lines made me think that u had already populated the contacts table.
and all u wanted to do was update the member table and expo table with cid's from contact table according to ur unique filed combination.

that is why if u check my post it queries from the contact table and just updates the member andexpo table accordingly
with conditions so no inserts where written...

but in ur latest post u mentioned that u need to insert records from member table to contacts table
" which by the way i assumed that u had already achieved [according to ur first paost ] "
and then insert expo records after checking for duplicates....

with all due repect buddy, i am not sure what u really want and so i am getting out of this thread..
i can give u the solution, but i don't want a situation when the whole requirement changes again..

Regards
Hart
any reason why this was closed?  Were you able to resolve the problem?

Despite Hart's comment, I think this is a good discussion.

Not everything is solved by a quick code post, that is why I usually like to start  a discussion on bigger problems.  There are many ways to approach the same issue but the goal is to find which path is the best for your situation.

btw I don't believe the requirements changed - the end goal is the same to link all three tables using cid, exid and mid.  The only thing changing is that we are discussing varying approaches, i.e. the design/process.  This happens in every project and if it doesn't, then chances are the project wasn't designed properly.

CJ

CJ, Thanks for your comment, however I did close it for the reasons of not wanting confrontation here on this thread.  This is not the place.  I also agree with you that the requirements have not change, the out come still reaches the same goal.  However the previous comment (not yours) has reminded me of when we used to go to the newsgroups and you got made to feel like you were to stupid to be there and made inferior.  I really dont like that attitude nor feeling.  I have been here for some years now and rarely have I been made to feel that way; actually never. however I thank all who have help me over the years.  In a nut shell I will continue to work on the solution that you have posted. and hopfully find my solution.  I beleive that it is best to close this thread, even if it is just to keep the atmosphere here in EE Plesant.

Thanks again, and if I can't get it to work I will just repost...

Thank you all.
<whole heartedly agreeing with your last post>

well, thank you for donating the points.  Post here if you need more help.  I believe you have my email address if you need specific questions answered on anything posted here.

CJ
thanks man.