SQL Insert question
Hi , this is puzzling me.. i have following table table name = Sierras. Columns = regno, platfrom, cost , licence_ref renewal_data i split this table up into 2: 1. Softwares cols= id, regno, platfrom, 2. Licences id, cost , licence_ref, renewal_data, i have a third table, a link table between softwares and licences cols= id, licence_id, software_id i want to populate this table with the ids from softwares and licences, so that if joined together 1 row would reflect a row in the old table sierras. What query can i write for this?? hope all that makes sense! regards IA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Insert question
You could create a view across these two tables to give you the info you need. Cheers, Kev. -Original Message- Sent: 07 February 2002 14:29 To: Multiple recipients of list ORACLE-L Hi , this is puzzling me.. i have following table table name = Sierras. Columns = regno, platfrom, cost , licence_ref renewal_data i split this table up into 2: 1. Softwares cols= id, regno, platfrom, 2. Licences id, cost , licence_ref, renewal_data, i have a third table, a link table between softwares and licences cols= id, licence_id, software_id i want to populate this table with the ids from softwares and licences, so that if joined together 1 row would reflect a row in the old table sierras. What query can i write for this?? hope all that makes sense! regards IA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Insert question
[EMAIL PROTECTED] wrote: Hi , this is puzzling me.. i have following table table name = Sierras. Columns = regno, platfrom, cost , licence_ref renewal_data i split this table up into 2: 1. Softwares cols= id, regno, platfrom, 2. Licences id, cost , licence_ref, renewal_data, i have a third table, a link table between softwares and licences cols= id, licence_id, software_id i want to populate this table with the ids from softwares and licences, so that if joined together 1 row would reflect a row in the old table sierras. What query can i write for this?? hope all that makes sense! regards IA insert into link_table (licence_id, software_id) select l.id, s.id from softwares s, licences l, sierras o where l.licence_ref = o.licence_ref and l.renewal_data = o.renewal_data and s.regno = o.regno and s.platform = o.platform (in case you would have the same regno on several platforms :)). I don't see why you need any 'id' column in the link table. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Insert question
Im afraid i cant answer that question Stephane , as i dont have much to do with the design of this DB... thanks for the query... regards IA Stephane Faroult [EMAIL PROTECTED]@fatcity.com on 07/02/2002 14:53:32 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: [EMAIL PROTECTED] wrote: Hi , this is puzzling me.. i have following table table name = Sierras. Columns = regno, platfrom, cost , licence_ref renewal_data i split this table up into 2: 1. Softwares cols= id, regno, platfrom, 2. Licences id, cost , licence_ref, renewal_data, i have a third table, a link table between softwares and licences cols= id, licence_id, software_id i want to populate this table with the ids from softwares and licences, so that if joined together 1 row would reflect a row in the old table sierras. What query can i write for this?? hope all that makes sense! regards IA insert into link_table (licence_id, software_id) select l.id, s.id from softwares s, licences l, sierras o where l.licence_ref = o.licence_ref and l.renewal_data = o.renewal_data and s.regno = o.regno and s.platform = o.platform (in case you would have the same regno on several platforms :)). I don't see why you need any 'id' column in the link table. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).