EaglePsyX EaglePsyX - 5 months ago 13
MySQL Question

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

Does somebody knows 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';

Answer

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
;