Subqueries (in / exists) to Joins

Question

SELECT *, `o_cheque_request.member_id`, `o_cheque_request.wallet_id`
FROM `o_cheque_request`, `o_member`, o_memberinfo`
WHERE `o_cheque_request.member_id` = `o_member.member_id`
AND `o_member.member_id` = `o_memberinfo.member_id`
AND withdraw_date >='2012-07-21'
AND `o_cheque_request.member_id`
IN (SELECT `member_id` FROM `o_cheque_request` GROUP BY `member_id` HAVING SUM(gross_amount ) <=10000)
GROUP BY `o_cheque_request.withdraw_date`, `o_cheque_request.member_id` ORDER BY `request_id` DESC

this takes to much time around 29 seconds how to reduce...using join... Guys please help me... Table Structure for o_cheque_request
request_id bigint(20) unsigned NOT NULL auto_increment,
wallet_id int(11) NOT NULL default '0',
member_id int(10) unsigned NOT NULL default '0',
withdraw_date date default NULL,
amount int(10) unsigned NOT NULL default '0',
gross_amount float(10,2) unsigned NOT NULL default '0.00',
admin_charge float(10,2) unsigned NOT NULL default '0.00',
tds float(10,2) unsigned NOT NULL default '0.00',
repurchase float(10,2) unsigned NOT NULL default '0.00',
net_amount float(10,2) unsigned NOT NULL default '0.00',
withdraw_type varchar(50) default NULL,
bank_name varchar(50) default NULL,
cheque_no varchar(50) default NULL,
courier_name varchar(50) default NULL,
tracking_no varchar(50) default NULL,
cheque_date date default '0000-00-00',
PRIMARY KEY (request_id),
KEY member_id (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4738 ;

29 secs to fetch how many records? and please provide your table structure. — Vishal Zanzrukia
Hey I have more than 4000 entries in o_cheque_request table... — Siddharth Joshi
Please provide table structure in proper way and for three tables with their internal relations which you have used in query. — Vishal Zanzrukia
Each Table have common field name member_id which connects them — Siddharth Joshi

Munees Majid Munees Majid · Accepted Answer · 2015-02-03T06:33:01

TRY WITH THIS

 SELECT * FROM (
        SELECT *, `o_cheque_request.member_id`, `o_cheque_request.wallet_id`
        FROM `o_cheque_request`
        JOIN `o_member` ON `o_cheque_request.member_id` = `o_member.member_id`
        JOIN `o_memberinfo` ON `o_member.member_id` = `o_memberinfo.member_id`
        WHERE withdraw_date >='2012-07-21'
        GROUP BY `o_cheque_request.member_id` HAVING SUM(`o_cheque_request.gross_amount` ) <=10000
    ) AS T
    GROUP BY `withdraw_date`, `member_id` ORDER BY `request_id` DESC

Subqueries (in / exists) to Joins

1 Answers