CODEWITHSUNDEEP
mysqlsubquerywhere-clause
EaglePsyX
EaglePsyX

Reputation: 45

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

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';

Upvotes: 0

Views: 54

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

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
;

Upvotes: 1

Related Questions