CODEWITHSUNDEEP
mysqljoinsubqueryin-subquery
Siddharth
Siddharth

Reputation: 368

Subqueries (in / exists) to Joins

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 ;

Upvotes: 1

Views: 83

Answers (1)

Munees Majid
Munees Majid

Reputation: 767

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

Upvotes: 0

Related Questions