SELECT * FROM (

                        (SELECT DISTINCT
                            'forwarded' as qfrom,
                            null as deals_id,
                            null as dealname,
                            null as oldprice,
                            NULL as offerprice,
                            NULL as endsat,
                            products.*, collectionname, designers.`name` AS designame, designers.hideprices,
                            (SELECT instock FROM prodstocks WHERE prodstocks.products_id = products.id AND prodstocks.instock>0 LIMIT 1 ) AS instock ,
                            if ((deals.products_id = products.id AND COALESCE(deals.prodsizes_id,0)=0 AND COALESCE(deals.colormaps_id,0)=0),1,0) as onsale,
                            collecgroupligs.colorder
                        FROM  products left JOIN deals on (deals.products_id=products.id),
                                    collections,designers,prodimages,prodscolors,colormap,collecgroups,collecgroupligs
                        WHERE collecgroups.id = 19
                            AND collecgroups.id = collecgroupligs.collecgroups_id
	                        AND collecgroupligs.collections_id = collections.id
                            AND products.collections_id = collections.id
                            AND designers.id = products.designers_id
                            AND products.id = prodimages.products_id 
                            AND products.id=prodscolors.products_id
                            AND prodscolors.colormaps_id=colormap.id
                            
                        GROUP BY products.id
                        HAVING onsale=0
                        )
                        UNION
                        (SELECT 
                        	'deals' as qfrom,
                        	deals.id as deals_id,
                            deals.dealname, 
                        	deals.oldprice,
                        	deals.offerprice,
                            deals.endsat,
                            products.*, collectionname, designers.`name` AS designame, designers.hideprices,
                            (deals.isinstock =1) as instock,
                            0 as onsale,
                            collecgroupligs.colorder
                        FROM
                        	products, deals , prodscategs,collections,designers,prodimages,prodscolors,colormap,collecgroups,collecgroupligs
                        WHERE collecgroups.id = 19
                            AND collecgroups.id = collecgroupligs.collecgroups_id
	                        AND collecgroupligs.collections_id = collections.id
                            AND products.id = prodscategs.products_id                             
                            AND products.collections_id = collections.id
                            AND designers.id = products.designers_id
                            AND products.id = prodimages.products_id 
                            AND deals.products_id=products.id AND deals.published = 1 AND deals.endsat >= curDate()
                            AND products.id=prodscolors.products_id
                            AND prodscolors.colormaps_id=colormap.id
                             
                            )
                         ) as search
                    ORDER BY  colorder, stylenum ASC
                     LIMIT 0, 29 
                    
SELECT count(distinct search.id) as total  FROM (

                        (SELECT DISTINCT
                            null as deals_id,
                            null as dealname,
                            null as oldprice,
                            NULL as offerprice,
                            NULL as endsat,
                            products.*, collectionname, designers.`name` AS designame, designers.hideprices
                            
                        FROM  products,collections,designers,prodimages, prodscolors,colormap,collecgroups,collecgroupligs
                        WHERE collecgroups.id = 19
                            AND collecgroups.id = collecgroupligs.collecgroups_id
	                        AND collecgroupligs.collections_id = collections.id
                            AND products.collections_id = collections.id
                            AND designers.id = products.designers_id
                            AND products.id = prodimages.products_id 
                            AND products.id=prodscolors.products_id
                            AND prodscolors.colormaps_id=colormap.id
                            
                        )
                        UNION
                        (SELECT 
                        	deals.id as deals_id,
                            deals.dealname, 
                        	deals.oldprice,
                        	deals.offerprice,
                            deals.endsat,
                            products.*, collectionname, designers.`name` AS designame, designers.hideprices
                             
                        FROM
                        	products, deals , prodscategs,collections,designers,prodimages,prodscolors,colormap,collecgroups,collecgroupligs
                        WHERE collecgroups.id = 19
                            AND collecgroups.id = collecgroupligs.collecgroups_id
	                        AND collecgroupligs.collections_id = collections.id
                            AND products.collections_id = collections.id
                            AND designers.id = products.designers_id
                            AND products.id = prodimages.products_id 
                            AND deals.products_id=products.id AND deals.published = 1 AND deals.endsat >= curDate()
                            AND products.id=prodscolors.products_id
                            AND prodscolors.colormaps_id=colormap.id
                             
                            )
                         ) as search

Tarik Ediz Mikonos 

There are 195 products.
Products you may be interested