[RESOLVED] complex mysql query... for me at least.

jmartinez

I have these three tables:

CREATE TABLE `projects` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(50) NOT NULL DEFAULT '',
  `work_performed` longtext,
  `original_amount` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`project_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

CREATE TABLE `project_additional` (
  `additional_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `selected` int(1) NOT NULL DEFAULT '0',
  `additional_description` longtext NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  `unit_measurement` varchar(5) DEFAULT NULL,
  `unit_cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `extended_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`additional_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

CREATE TABLE `invoice_transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `transaction_date_time` datetime DEFAULT '0000-00-00 00:00:00',
  `transaction_type` varchar(50) DEFAULT NULL,
  `transaction_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `transaction_memo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

What I am attempting to do is get the total still due for ALL projects with a specific status. The projects table holds the "original_amount" of the project and defines the status of the project, the project_additional table holds all additional items for the project and the prices of those items, the invoice_transactions table well.... holds all the invoice transactions.

Here is what needs to happen...

1: Get the total of the "original_amounts" for all projects with a status of "foo".
2: Get the total of all the extended_prices from the project_additional table for the items that have been selected for these projects (project_additional.selected=1 or yes).
3: Get the total of all transactions for each transaction_type (payments, credits and charges) for these projects.
4: Display the total still due for all these projects after this formula has been applied:

projects.original_amount+project_additional.extended_price(for all selected items)+invoice_transactions.transaction_amount(charges)-invoice_transactions.transaction_amount(credits)-invoice_transactions.transaction_amount(payments)

Note: in the formula above I show project_additional.extended_price(for all selected items), invoice_transactions.transaction_amount(charges), invoice_transactions.transaction_amount(credits) and invoice_transactions.transaction_amount(payments). I realize that I am misusing the () part, what I am trying to show is what types of transaction_type exist and that it needs to include all selected items from project_additional.

I am capable of doing this on a single specific record but am at a complete loss on how to do this for all projects of a certain status (status=foo). I'm looking for a MySQL ninja for sure!

bradgrafelman

Few things.. why are things like transaction_type VARCHAR fields? Those should be ENUM fields at the very least (better could even be a foreign key to a transaction_type table). The same may or may not apply to the project 'status' column as well. Speaking of foreign key constraints... why don't you have any? Additionally, why is transaction_type a NULL column? What good does a transaction amount do for you if you don't know what type of transaction it was?

On to the query... it's possible that it could be simplified using JOINs, but it's either way too close (or beyond) my bedtime or I've just been away from SQL for far too long, but here's what I came up with using subqueries:

SELECT
    p.project_id project_id,
    (
        p.original_amount
        + COALESCE(
          ( SELECT
                SUM(extended_price)
            FROM
                project_additional pa
            WHERE
                pa.project_id = p.project_id
                AND pa.selected = 1
           ), 0 )
         + COALESCE(
           ( SELECT
                 SUM(transaction_amount)
             FROM
                 invoice_transactions i_charges
             WHERE
                 i_charges.project_id = p.project_id
                 AND i_charges.transaction_type = 'charge'
           ), 0 )
         - COALESCE(
           ( SELECT
                 SUM(transaction_amount)
             FROM
                     invoice_transactions i_credits
             WHERE
                 i_credits.project_id = p.project_id
                 AND i_credits.transaction_type = 'credit'
           ), 0 )
         - COALESCE( 
          ( SELECT
                 SUM(transaction_amount)
                 FROM
                 invoice_transactions i_payments
             WHERE
                 i_payments.project_id = p.project_id
                 AND i_payments.transaction_type = 'payment'
           ), 0 )
    ) balance
FROM
    projects p
WHERE
    p.status = 'foo'

jmartinez

To be very honest, I am a MySQL NEWB well.. kinda. More like I never studied more that what I needed for the task at hand and up until a while back the task at hand was relatively small. I would agree with you 110% if you were to say I needed to brush up on MySQL fundamentals. In fact I am actually starting to with a friend of mine.

I truly started this project long ago when all I knew how to do was create a simple table, insert, update and delete records. I am that guy who tried to learn to run before walk... yup, I'm an idiot LOL

I am/was a hobby programmer who has a non development/mysql day job that takes a huge chunk of my time so moving forward with my skills has been extremely slow and spotty at best.

The results I get from your query are what I expected to see. The only thing different that I would like to see is the totals of those results as one single total rather than multiple results.

jmartinez

OK my friend and I got it to display like I was needing it to.

You sir have taught me a lot just from being able to read your query. I really do appreciate the help, honestly! Could you recommend a good jumping off point to get me back to the MySQL basics that I should have learned? Maybe a favorite book of yours or ??

Also, I would like to compensate you for your time. If you have paypal I would love to send you a little something for the help, i'll buy ya a beer! Seriously, you deserve it.

bradgrafelman

I never really did get into books when it comes to learning things like PHP or SQL, although the latter was covered in a couple of classes I took while obtaining my degree in computer science.

And the offer of compensation isn't necessary (nor allowed here at PHPBuilder, except in the "Available Positions" forum 😉) but appreciated nonetheless.

jmartinez

bradgrafelman;11010523 wrote:
I never really did get into books when it comes to learning things like PHP or SQL

Yeah im not much of a book person either, I much rather jump right in and learn by doing or failing.

bradgrafelman;11010523 wrote:
And the offer of compensation isn't necessary (nor allowed here at PHPBuilder, except in the "Available Positions" forum 😉) but appreciated nonetheless.

Well... crap... I will keep that in mind for next time. Thanks again man!