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
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,