|
electriciansnet
Starting Member
2 Posts |
 Posted - 2007-05-23 : 01:59:47
|
| Hello All!I have done lots of reasearch on relational (one-to-many) databases and I cannot seem to find the correct Insert syntax.I'm running (locally) ColdFusion7 and SQL 2005 both on the same machine on my LAN.Here is my structure:dbo.contractorInfomemberID [PK , INT] companyName......... dbo.ZipszipID/ [PK/ autonumber] zipCode 1 33614 2 33615 3 44613 4 44614 etc. (1 Insert can populate hundreds of rows of zips, zipIDS) MY PROBLEM TABLEdbo.ContractorZipmemberZipID[PK,autonumber]memberID [FK_contractor_Info.memberID] zipID [FK_ZipCodes.ZipID]___________I have the INSERT with values down ok but I cannot figure out how to insert into table_contractor_Zip.Here is what I'm trying to accomplish:The consumer goes to my home page looking for an electrical contractor in their area. There is 1 input field,(#form.myZip#), which is how they are to search my db. I got that all figured out ok with a simple WHERE statement when it was all on 1 table.The problem is that any given contractor can have over a hundred zips and every zip can have up to 3 contractors. I have a downloaded (treated as read only) table containing 42000 zips which is what the contractor queries against with his state, i.e. #form.states_dropdown#. That works fine too. My query loops through the zips stored in the previous page in a comma delimited field and inserts them row by row, ever increasing the zipID by increments in the hundres. This is where the relational db method escapes me. Is it supposed to be memberID 1 belongs to zipIDs 1-whatever? Then how do I relate that to the memberZipID? Even if I knew what was going on, I still don't know how to insert it.Here are the main snippets from my enroll.cfm page (please ignore open tags as I have left out unrelated code):tbl_contractor_Info<cfquery name="getMax" datasource="#enet#">select max(#KeyM#) as Max1from #TableI#</cfquery><cfif len(getMax.Max1)><cfset ID1=getMax.Max1+1><cfelse><cfset ID1=1></cfif><cfquery name="insertMemberData" datasource="#enet#">INSERT INTO dbo.contractor_Info (memberID,companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, License, CellNumber, NetworkID, Slogan)VALUES (#ID1#, '#Form.company#', '#form.address#', '#form.city#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.url#', '#form.license#', '#form.cell_phone#', '#form.carrier#', '#slogan#')</cfquery>(works fine)tbl_zips<cfloop index = "ListElement" list = "#form.zipCodes#"delimiters=","> <cfquery name="insertZipCodes" datasource="#enet#"> INSERT INTO dbo.zips (zipCode)VALUES (<cfoutput>'#ListElement#'</cfoutput>)</cfquery></cfloop>(zipID is set to isIdentity (atutonum) so you do not see it here)(also works fine)tbl_contractor_zip (the problem) <cfquery name="insertIDs" datasource="#enet#"> INSERT INTO contractor_Zip (zipID, memberID)VALUES (#getNewID.zipID#,#ID1#)</cfquery> <cfquery name="insertIDs" datasource="#enet#"> INSERT INTO contractor_Zip (zipID, memberID)VALUES (#getNewID.zipID#,#ID1#)</cfquery>(This inserted the max zip ID with the member id which I may have been able to work with but it put the same ID in on the next transaction (and again memberZipID is autonum)) So I let SQL make this last statement for me but it did nothing at all:INSERT INTO contractor_Zip (memberID, zipID)SELECT DISTINCT ElectricalContractorInfo.memberID AS memberID, zipCodes.zipID AS zipIDFROM contractor_Zip AS contractor_Zip_1 INNER JOIN ElectricalContractorInfo ON contractor_Zip_1.memberID = ElectricalContractorInfo.memberID INNER JOIN zipCodes ON contractor_Zip_1.zipID = zipCodes.zipIDORDER BY zipID, memberIDIs this something that I'm supposed to be doing in SQL and not even bothering with an INSERT statement for this table?***Note: all tables created manually in Studio 2005Please help. Thanks, Andy |
|
Insertion of foriegn keys