IS there anyway i can improve this query?
SELECT DISTINCT adb_product_type.strproduct_type AS strproduct_type, adb_product_cat.strproduct_cat AS strproduct_cat, adb_product.strproduct AS strproduct,
CASE @campaignCount:=
(SELECT COUNT(DISTINCT adb_campaign_1.campaign_id)
FROM adb_camp_media AS adb_camp_media_1
LEFT JOIN adb_campaign AS adb_campaign_1 ON adb_campaign_1.campaign_id = adb_camp_media_1.campaign_id
LEFT JOIN adb_camp_media_prod AS adb_camp_media_prod_1 ON adb_camp_media_prod_1.media_num = adb_camp_media_1.media_num
WHERE
(adb_camp_media_1.inttotal_per_store = 1 AND adb_camp_media_1.lngproduct = adb_product.lngproduct OR adb_camp_media_1.inttotal_per_store > 1 AND adb_camp_media_prod_1.lngproduct = adb_product.lngproduct)
AND (
(
adb_campaign_1.start_date
BETWEEN '2011-01-01'
AND '2011-01-31'
)
OR (
adb_campaign_1.end_date
BETWEEN '2011-01-01'
AND '2011-01-31'
)
OR (
adb_campaign_1.start_date < '2011-01-01'
AND adb_campaign_1.end_date > '2011-01-31'
)
)
)
WHEN 0 THEN 'No' ELSE 'Yes' END AS 'YesNo',
CASE @campaignCount WHEN 0 THEN '' ELSE @campaignCount END AS 'CampaignCount',
CASE @campaignCount WHEN 0 THEN '' ELSE
(SELECT GROUP_CONCAT(DISTINCT adb_media_1.name ORDER BY adb_media_1.name SEPARATOR ', ' )
FROM adb_camp_media AS adb_camp_media_1
LEFT JOIN adb_campaign AS adb_campaign_1 ON adb_campaign_1.campaign_id = adb_camp_media_1.campaign_id
LEFT JOIN adb_camp_media_prod AS adb_camp_media_prod_1 ON adb_camp_media_prod_1.media_num = adb_camp_media_1.media_num
LEFT JOIN adb_media AS adb_media_1 ON adb_media_1.media_id = adb_camp_media_1.media_id
WHERE
(adb_camp_media_1.inttotal_per_store = 1 AND adb_camp_media_1.lngproduct = adb_product.lngproduct OR adb_camp_media_1.inttotal_per_store > 1 AND adb_camp_media_prod_1.lngproduct = adb_product.lngproduct)
AND (
(
adb_campaign_1.start_date
BETWEEN '2011-01-01'
AND '2011-01-31'
)
OR (
adb_campaign_1.end_date
BETWEEN '2011-01-01'
AND '2011-01-31'
)
OR (
adb_campaign_1.start_date < '2011-01-01'
AND adb_campaign_1.end_date > '2011-01-31'
)
)
)
END AS mediaName , adb_product.lngproduct
FROM adb_product_type
LEFT JOIN adb_product_cat ON adb_product_cat.lngproduct_type = adb_product_type.lngproduct_type
LEFT JOIN adb_product ON adb_product.lngproduct_cat = adb_product_cat.lngproduct_cat
LEFT JOIN adb_camp_media ON adb_camp_media.lngproduct = adb_product.lngproduct
LEFT JOIN adb_media ON adb_media.media_id = adb_camp_media.media_id
LEFT JOIN adb_camp_media_prod ON adb_camp_media_prod.media_num = adb_camp_media.media_num
LEFT JOIN adb_campaign ON adb_campaign.campaign_id = adb_camp_media.campaign_id
WHERE 1=1
ORDER BY YesNo DESC , strproduct_type, strproduct_cat, strproduct
TABLE STRUCTURE
-- Table structure for table adb_camp_media
CREATE TABLE adb_camp_media ( media_num int(11) NOT NULL auto_increment, campaign_id int(11) NOT NULL default '0', media_id int(11) NOT NULL default '0', inttotal_per_store int(4) NOT NULL default '0', units_per_item int(2) default NULL, lngproduct int(11) NOT NULL default '0', rental_cost double(10,2) default NULL, checkers_rental double default NULL, hyper_rental double default NULL, usave_rental double default NULL, ls_rental double(10,2) default '0.00', spar_rental double(10,2) default '0.00', backboard tinyint(1) NOT NULL, PRIMARY KEY (media_num), KEY campaign_id (campaign_id,media_id,lngproduct), KEY lngproduct (lngproduct) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table adb_camp_media_prod
CREATE TABLE adb_camp_media_prod ( media_prod_id int(11) NOT NULL auto_increment, media_num int(4) NOT NULL, lngproduct int(4) NOT NULL, PRIMARY KEY (media_prod_id), KEY media_num (media_num,lngproduct), KEY lngproduct (lngproduct) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table adb_campaign
CREATE TABLE adb_campaign ( campaign_id int(11) NOT NULL auto_increment, client_id int(11) NOT NULL default '0', campaign_name varchar(50) NOT NULL default '', description text, position_desc text, start_date date NOT NULL default '0000-00-00', end_date date NOT NULL default '0000-00-00', approved tinyint(1) NOT NULL default '0', created_date date NOT NULL default '0000-00-00', updated_date datetime default NULL, user_id int(11) NOT NULL default '0', pinno varchar(50) NOT NULL default '', jobno varchar(30) NOT NULL default '', quoteno varchar(30) NOT NULL default '', sales_ae_id int(11) default NULL, artwork_specno varchar(30) NOT NULL default '', artwork_jobno varchar(30) NOT NULL default '', agency tinyint(1) NOT NULL default '0', brand_name varchar(50) NOT NULL default '', paypercycle tinyint(1) NOT NULL default '0', agency_comm double default NULL, variant varchar(100) default NULL, pack_size varchar(100) default NULL, discount double default NULL, discount_val double default NULL, sales_comm double NOT NULL default '0', cancelled tinyint(1) default '0', onhold tinyint(1) default '0', campaign_status_id int(4) default NULL, PRIMARY KEY (campaign_id), UNIQUE KEY jobno (jobno), KEY start_date (start_date), KEY end_date (end_date), KEY campaign_name (campaign_name), KEY user_id (user_id,cancelled,onhold) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table adb_product
CREATE TABLE adb_product ( lngproduct int(11) NOT NULL auto_increment, strproduct varchar(100) NOT NULL default '', lngproduct_cat int(11) NOT NULL default '0', PRIMARY KEY (lngproduct), KEY lngproduct_cat (lngproduct_cat) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table adb_product_cat
CREATE TABLE adb_product_cat ( lngproduct_cat int(11) NOT NULL auto_increment, strproduct_cat varchar(100) NOT NULL default '', lngproduct_type int(11) NOT NULL default '0', PRIMARY KEY (lngproduct_cat), KEY lngproduct_type (lngproduct_type) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table adb_product_type
CREATE TABLE adb_product_type ( lngproduct_type int(11) NOT NULL auto_increment, strproduct_type varchar(100) NOT NULL default '', PRIMARY KEY (lngproduct_type) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
