SELECT distinct * FROM 
                      ( 
                        (SELECT 
                            'forwarded' as qfrom,
                            null as deals_id,
                            null as dealname,
                            null as oldprice,
                            NULL as offerprice,
                            NULL as endsat,
                            products.*, 0 as showorder, collectionname, collections.corder,categprods_order.porder as prdorder, designers.`name` AS designame, designers.hideprices,  designers.prefix,  
                            (SELECT instock FROM prodstocks WHERE prodstocks.products_id = products.id AND prodstocks.instock>0 LIMIT 1 ) AS instock ,
                            0 as onsale
                        FROM categprods_order LEFT JOIN products ON ( products.id = categprods_order.products_id )
                                LEFT JOIN collections ON ( products.collections_id = collections.id )
                                LEFT JOIN designers ON ( designers.id = products.designers_id )
                                INNER JOIN prodimages ON ( prodimages.products_id = categprods_order.products_id )
                                INNER JOIN prodscolors ON (prodscolors.products_id=products.id)
                                INNER JOIN colormap ON (colormap.id=prodscolors.colormaps_id)
                        WHERE categprods_order.categories_id = 15 
                                AND (categprods_order.opage=1 OR categprods_order.opage=0)
                                AND categprods_order.products_id>0 
                                AND products.id=prodscolors.products_id
                                AND prodscolors.colormaps_id=colormap.id
                                
                        GROUP BY prodimages.products_id
                         LIMIT 0, 29 
                        )
                        UNION
                        (SELECT 
                                'deals' as qfrom,
                            	deals.id as deals_id,
                                deals.dealname, 
                            	deals.oldprice,
                            	deals.offerprice,
                                deals.endsat,
                                products.*, 1 as showorder, collectionname, collections.corder, null  as prdorder, designers.`name` AS designame, designers.hideprices,  designers.prefix,  
                                (deals.isinstock =1) as instock,
                                0 as onsale
                            FROM
                            	products, deals , prodscategs,collections,designers,prodimages, categories, prodscolors, colormap
                            WHERE products.id = prodscategs.products_id
                                AND products.collections_id = collections.id
                                AND designers.id = products.designers_id
                                AND products.id = prodimages.products_id 
                                AND categories.id=prodscategs.categories_id
                                AND prodscategs.categories_id = 15
                                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
                                
                            GROUP BY prodimages.products_id
                            LIMIT 5
                         )
                         
                        UNION
                        (SELECT 
                                'categ' as qfrom,
                                null as deals_id,
                                null as dealname,
                                null as oldprice,
                                NULL as offerprice,
                                NULL as endsat,
                                products.*, 3 as showorder, collectionname, collections.corder, null  as prdorder, designers.`name` AS designame,  designers.hideprices,  designers.prefix,  
                                (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
                            FROM products left JOIN deals on (deals.products_id=products.id),
                                    prodscategs,collections,designers,prodimages, prodscolors, colormap
                            WHERE products.id = prodscategs.products_id
                                AND prodscategs.categories_id = 15
                                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
                             LIMIT 0, 29 
                         )
                       ) as Category
                     ORDER BY Category.showorder ASC, Category.prdorder, Category.corder, stylenum ASC
                     LIMIT 29    
                        ;
homecoming dresses 2015

homecoming dresses 2015 

There are 267 products.
Products you may be interested