subqueries.sql

    /* Conditional */
    SELECT product_id, language_code, name, custom_name, description, short_description FROM product_translations
    WHERE product_id=16741 AND 
        language_code = SELECT IF ((SELECT COUNT(*) FROM product_translations WHERE product_id=16741), 'es', 'en_US');
        
        
        
    /*With Join*/
    
    ELECT DISTINCT pro.product_id, pcr.category_id,pro.product_sku, 
                pro.sort_order, pro.product_custom_qr, pro.product_price, pro.product_price, pro.product_discount, 
                pro.child_products, pro.sort_order, pro.related_products, pro.product_show_brand_logo, 
                pro.product_show_description, pro.product_show_name, pro.product_show_price, 
                pro.product_show_qr_code, pro.product_show_sku,
                pi.media_id, pi.media_path, pi.media_url, pi.media_type,
                pt.name, pt.short_description
                FROM product_category_relations as pcr
                JOIN products as pro ON (pcr.product_id = pro.product_id AND pro.deleted = 0) 
                LEFT JOIN product_images as pi ON (pi.product_id = pro.product_id AND pi.media_option = 'list')
                LEFT JOIN product_translations as pt ON(pt.product_id = pro.product_id AND 
                   pt.language_code = (SELECT IF ((SELECT COUNT(*) FROM product_translations WHERE product_id=pro.product_id), 'es', 'en_US'))
                 )
                WHERE pcr.category_id = 1288 
                ORDER BY pro.sort_order