Untitled

mail@pastecode.io avatarunknown
plain_text
2 months ago
1.6 kB
0
Indexable
Never
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `toy_store`.`productwithprice` AS
    SELECT 
        `t`.`SKU` AS `SKU`,
        `t`.`Name` AS `Name`,
        `t`.`Status` AS `Status`,
        `t`.`Description` AS `Description`,
        `t`.`InventoryNumber` AS `InventoryNumber`,
        `t`.`Image` AS `Image`,
        `t`.`BrandId` AS `BrandId`,
        `t`.`SupplierId` AS `SupplierId`,
        `t`.`IsNew` AS `IsNew`,
        `pp`.`Price` AS `Price`,
        `pro`.`PercentDiscount` AS `PercentDiscount`
    FROM
        ((`toy_store`.`toy` `t`
        JOIN `toy_store`.`productprice` `pp` ON ((`t`.`SKU` = `pp`.`SKU`)))
        LEFT JOIN (SELECT 
            `toy_store`.`promotiondetail`.`PercentDiscount` AS `PercentDiscount`,
                `toy_store`.`promotiondetail`.`SKU` AS `SKU`
        FROM
            (`toy_store`.`promotion`
        JOIN `toy_store`.`promotiondetail`)
        WHERE
            ((`toy_store`.`promotion`.`DateStart` <= NOW())
                AND (`toy_store`.`promotion`.`DateEnd` >= NOW())
                AND (`toy_store`.`promotion`.`PromotionId` = `toy_store`.`promotiondetail`.`PromotionId`))) `pro` ON ((`t`.`SKU` = `pro`.`SKU`)))
    WHERE
        (`pp`.`UpdatedDate` = (SELECT 
                MAX(`toy_store`.`productprice`.`UpdatedDate`)
            FROM
                `toy_store`.`productprice`
            WHERE
                ((`toy_store`.`productprice`.`SKU` = `t`.`SKU`)
                    AND (`toy_store`.`productprice`.`UpdatedDate` <= NOW()))))