MySQL statement - Help

[defcon2000]

Hello Everyone,

 

I want to insert a new record with data from 2 tables, based on a condition from a table . The insert should be as follows:

 

INSERT INTO subscriptiontrans (subscriptiontrans_id, subscription_id,

datecreated, paymentdue,

datepaid, subscriptiontype_id,

subscriptiondescription, service_description,

occurence, occurencetype,

currencysymbol, amount, amountlocal, amountdiscount,

amounttax, amountnet, subscriptiontrans_status, printed)

VALUES (????????????)

 

The tables to get data from are as follows:

--------------------------------------------------

Table name: subscription

`subscription_id`

`customer_id`

`datecreated`

`datedue`

`subscriptiontype_id`

`amountdiscount`

`specialnotes`

`subscription_status`

--------------------------------------------------

Table name: subscriptiontype

`subscriptiontype_id`

`subscription_type`

`subscriptiondescription`

`occurence`

`occurencetype`

`currency_id`

`amount`

`taxpercent`

`subscriptiontype_status`

--------------------------------------------------

Table name: currency

`currency_id`

`currency`

`currencysymbol`

`rate`

--------------------------------------------------

 

The conditions for insert should be as follows:

 

subscription.datedue = CURDATE()

subscription.subscription_status = 'Active'

 

The following are calculations and defaults:

 

subscription_id = subscription.subscription_id

datecreated = CURDATE()

paymentdue = 7 days from CURDATE()

datepaid = NULL

subscriptiontype_id = subscriptiontype.subscriptiontype_id

subscriptiondescription = subscriptiontype.subscriptiondescription

service_description = subscriptiontype.service_description

occurence = subscriptiontype.occurence

occurencetype = subscriptiontype.occurencetype

currencysymbol = currency.currencysymbol

amount = subscriptiontype.amount

amountlocal = currency.rate * subscriptiontype.amount

amountdiscount = subscription.amountdiscount

amountnet = (amountlocal - amountdiscount) * ((100 - amounttax)/100)

subscriptiontrans_status = 'Unpaid'

printed = 'N'

 

Thanks in advance for your help.

 

Rgds,

[fenway]

See [a href=\"http://www.mysqlfreaks.com/forums/index.php?showtopic=8900&hl=\" target=\"_blank\"]existing thread[/a] on the MySQLFreaks.com forum for my response.