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,ema il 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="uspoultryintra net">
Select * from currentex
</cfquery>
<cfoutput query="getex">
<cfquery name="getcn" datasource="uspoultryintra net">
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--->
(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,ema
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="uspoultryintra
Select * from currentex
</cfquery>
<cfoutput query="getex">
<cfquery name="getcn" datasource="uspoultryintra
Select contactid from curcontacts
where
(lname='#Getex.lname#')and
(fname='#Getex.fname#')and
(address1='#Getex.address1
(city='#Getex.city#')and
(State='#Getex.State#')and
(email='#Getex.email#')
</cfquery>
</cfoutput>
<!--- update expo table with new CID goes here--->
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="uspoultryintra net">
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="uspoultryintra net">
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
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="uspoultryintra
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="uspoultryintra
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
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
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
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 common data linking the two legacy DBs is what?
CJ
ASKER
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
CJ
ASKER
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
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
ASKER
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???
:^|
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
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
ASKER
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.
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
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
ASKER
will test and let you know.
k, headed out to dinner soon, will check for updates in a couple of hours or so.
CJ
CJ
did u event look at my post ???
did u event look at my post ???
ASKER
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
not yet I havent worked on it since the morn I have encountered 2 other fires (down servers).
I will let you know.
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,ema il 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
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,ema
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
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
ASKER
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.
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
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
ASKER
thanks man.
<CFQUERY NAME="getcn" DATASOURCE="uspoultryintra
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>