gistfile1.sql

SELECT DISTINCT 
  `SiteTree_Live`.`ClassName`,
  `SiteTree_Live`.`Created`,
  `SiteTree_Live`.`LastEdited`,
  `SiteTree_Live`.`URLSegment`,
  `SiteTree_Live`.`Title`,
  `SiteTree_Live`.`MenuTitle`,
  `SiteTree_Live`.`Content`,
  `SiteTree_Live`.`MetaDescription`,
  `SiteTree_Live`.`ExtraMeta`,
  `SiteTree_Live`.`ShowInMenus`,
  `SiteTree_Live`.`ShowInSearch`,
  `SiteTree_Live`.`Sort`,
  `SiteTree_Live`.`HasBrokenFile`,
  `SiteTree_Live`.`HasBrokenLink`,
  `SiteTree_Live`.`ReportClass`,
  `SiteTree_Live`.`CanViewType`,
  `SiteTree_Live`.`CanEditType`,
  `SiteTree_Live`.`Priority`,
  `SiteTree_Live`.`Version`,
  `SiteTree_Live`.`ParentID`,
  `Product_Live`.`Price`,
  `Product_Live`.`Weight`,
  `Product_Live`.`Model`,
  `Product_Live`.`Quantifier`,
  `Product_Live`.`FeaturedProduct`,
  `Product_Live`.`AllowPurchase`,
  `Product_Live`.`InternalItemID`,
  `Product_Live`.`NumberSold`,
  `Product_Live`.`FullSiteTreeSort`,
  `Product_Live`.`FullName`,
  `Product_Live`.`ShortDescription`,
  `Product_Live`.`AllCountries`,
  `Product_Live`.`ImageID`,
  `SiteTree_Live`.`ID`,
  CASE
    WHEN `SiteTree_Live`.`ClassName` IS NOT NULL 
    THEN `SiteTree_Live`.`ClassName` 
    ELSE 'SiteTree' 
  END AS `RecordClassName` 
FROM
  `SiteTree_Live` 
  LEFT JOIN `Product_Live` 
    ON `Product_Live`.`ID` = `SiteTree_Live`.`ID` 
  INNER JOIN `Product_IncludedCountries` 
    ON `Product_IncludedCountries`.`ProductID` = `SiteTree_Live`.`ID` 
  LEFT JOIN `EcommerceCountry` 
    ON `Product_ExcludedCountries`.`EcommerceCountryID` = `EcommerceCountry`.`ID` 
  INNER JOIN `Product_ExcludedCountries` 
    ON `Product_ExcludedCountries`.`ProductID` = `SiteTree_Live`.`ID` 
WHERE (
    (
      `Product_Live`.`AllCountries` = '1'
    ) 
    OR (`EcommerceCountry`.`Code` = 'NZ')
  ) 
  AND (
    (
      `EcommerceCountry`.`Code` != 'NZ'
    )
  ) 
  AND (
    `SiteTree_Live`.`ClassName` IN ('Product')
  ) 
ORDER BY `SiteTree_Live`.`Sort` ASC