Jump to content


Photo

MySQL statement - Help


  • Please log in to reply
1 reply to this topic

#1 defcon2000

defcon2000
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 05 January 2006 - 05:56 PM

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,

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 January 2006 - 07:17 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users