Created
August 15, 2012 18:09
-
-
Save mbiang/3362029 to your computer and use it in GitHub Desktop.
New Big Query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT SQL_NO_CACHE DISTINCT concat(products.sName, ' ', product_variations.sName) AS title, product_variations.sProductNumber AS id, products.iProductID AS iProductID, product_variations.iVariationID AS iVariationID, product_variations.fWeight AS weight, product_variations.upc AS upc, product_variations.mpn AS mpn, product_availabilities.sFeedAvailability AS availability, products.sFriendlyName AS link, image_links.iImageID AS image_link, products.sDescription AS description, products.sMoreInfo AS more_info, pvp1.dPrice AS price, 'US::Ground:6.99,US::2nd Day:14.99,US::Next Day:19.99' AS shipping, 'Visa, MasterCard, AmericanExpress, Discover' AS payment_accepted, 'pet supplies' AS product_type, manufacturers.sName AS primary_manufacturer, products.bPrescription AS prescription, products.bCompound AS compound, products.bClassIV AS classiv | |
FROM product_variations INNER JOIN products ON products.iProductID = product_variations.iProductID INNER JOIN product_availabilities ON product_variations.iAvailabilityID = product_availabilities.iAvailabilityID INNER JOIN image_links ON products.iProductID = image_links.iItemID LEFT JOIN manufacturers ON product_variations.iPrimaryManufacturerID = manufacturers.iManufacturerID INNER JOIN product_variation_prices pvp1 ON product_variations.iVariationID = pvp1.iVariationID WHERE product_variations.bDeleted = 0 AND products.bDeleted = 0 AND product_variations.iAvailabilityID > 0 AND products.iStateID = 0 AND product_variations.iStateID = 0 AND pvp1.bDeleted = 0 AND image_links.iImageTypeID = 1 AND image_links.bDefault = 1 AND NOT EXISTS ( | |
SELECT 1 FROM product_variation_prices pvp2 WHERE pvp1.iVariationID = pvp2.iVariationID AND ((pvp1.dEffective < pvp2.dEffective) OR (pvp1.dEffective = pvp2.dEffective AND pvp2.iPriceID > pvp1.iPriceID)) AND pvp2.dEffective <= now() AND pvp2.bDeleted <> 1 ) | |
AND product_variations.sProductNumber LIKE '%153.00614%' ORDER BY title |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment