Is there a possibility to avoid two subqueries which access the same table?

Question

How to improve this mysql view?

I think it's not necessary to perform two subqueries which accessing both to the same table jc_donation_method with the same WHERE CLAUSE = d.donation_method = m.donation_method but different SELECT CLAUSEL. But I've no idea to avoid this.

donation_id is a primary key in jc_donation and a foreign key in jc_donation_method.

CREATE
  OR REPLACE
  ALGORITHM = MERGE
  VIEW jc_donation_total AS
SELECT
  d.donation_method,
  (SELECT
      m.method_name
   FROM
      `jc_donation_method` m
   WHERE
      d.donation_method = m.donation_method
  LIMIT 1) method_name,
  CAST(SUM(d.donation_amount-
    (SELECT
        m.method_fee_nonrecurring
     FROM
        `jc_donation_method` m
     WHERE
        d.donation_method = m.donation_method
     LIMIT 1)
  - d.donation_amount*(
  (SELECT
      m.method_fee_percent
   FROM
      `jc_donation_method` m
   WHERE
      d.donation_method = m.donation_method
   LIMIT 1))
  ) as decimal(12,4)) donation_total
FROM
  `jc_donation` d
LEFT JOIN
  `jc_user` u
ON
  d.user_id = u.user_id
GROUP BY
  d.donation_method
HAVING
  COUNT(u.user_id) > 0

Basically I would like to know all overall donations which are done by active users per donation_method: amount - nonrecurring fees - percent fees % (per single donations).

Prerequisite:

CREATE TABLE `jc_user` (
  `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User credentials';

CREATE TABLE `jc_donation_method` (
  `donation_method` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `method_name` varchar(32) NOT NULL,
  `method_fee_percent` decimal(6,4) NOT NULL DEFAULT 0.00,
  `method_fee_nonrecurring` decimal(5,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`donation_method`) USING BTREE,
  UNIQUE KEY `method_name` (`method_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Donation methods and fees';

INSERT INTO
  `jc_donation_method` (`donation_method`, `method_name`,
  `method_fee_percent`, `method_fee_nonrecurring`)
VALUES
  (NULL, 'Transfer',0.000,0.00),
  (NULL, 'PayPal',0.0190,0.35);

CREATE TABLE `jc_donation` (
  `donation_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `donation_method` int(10) UNSIGNED NOT NULL DEFAULT '1',
  `donation_amount` decimal(12,4) NOT NULL,
  PRIMARY KEY (`donation_id`) USING BTREE,
  FOREIGN KEY (user_id) REFERENCES jc_user(user_id),
  FOREIGN KEY (donation_method) REFERENCES jc_donation_method(donation_method)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Donations';

Ordinarily, right now I would be talking about INNER JOIN, but the fly in that ointment seems to be the LIMIT 1 clause in each of the nested queries. ## Having said that, my personal bottom line is (to quote Seuss' Green Eggs and Ham): "I do not like the smell of this view, Sam-I-Am." I think that the logic should be using INNER JOIN, but this necessarily means that all consumers of this view, anywhere in the application, must be prepared to see more-than-one row in the result. — Mike Robinson
Okay, there is table containing all donations jc_donations by donation_id. In another table jc_user all users are stored. Every user is able to make several donations. Every donation can based on different methods (donation_method) in jc_donation_method` like bank transfer or paypal. Most of the payment sites charges some fees. Like nonrecurring fees for every transfer or percental fees. Some of them both (Paypal). This view should represents the total income after the fees per donation_method. — EaglePsyX
Inner join would be the right method and it seems like you'd want to inner join to a sub query that aggregates up to the level you want so that only one record comes through. — Jeff

Uueerdo Uueerdo · Accepted Answer · 2016-06-27T22:52:20

As I said in my initial comment, I am not sure why the user is relevant, but this should be the simplest query to get what you want:

SELECT m.donation_method, m.method_name
  , CAST(
      d.donation_amount 
      - m.method_fee_nonrecurring 
      - (d.donation_amount * m.method_fee_percent)
    AS DECIMAL(12, 4)) donation_total
FROM jc_donation_method AS m
INNER JOIN jc_donation AS d
ON m.donation_method = d.donation_method
GROUP BY m.donation_method
;

The version below should factor in users like your original did.

SELECT m.donation_method, m.method_name
  , CAST(
      d.donation_amount 
      - m.method_fee_nonrecurring 
      - (d.donation_amount * m.method_fee_percent)
    AS DECIMAL(12, 4)) donation_total
FROM jc_donation_method AS m
INNER JOIN jc_donation AS d
INNER JOIN jc_user AS u ON d.user_id = u.user_id
ON m.donation_method = d.donation_method
GROUP BY m.donation_method
;

Is there a possibility to avoid two subqueries which access the same table?

1 Answers