Skip to content

Instantly share code, notes, and snippets.

@srttk

srttk/subqueries.sql

Last active March 23, 2019 09:17
Show Gist options
  • Save srttk/53e171fd80bd5dfbee9dd33cd3867003 to your computer and use it in GitHub Desktop.
Save srttk/53e171fd80bd5dfbee9dd33cd3867003 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment