An SQL query generated by Magento's tax calculation resource model on a shop that uses the FireGento_GermanSetup extension. This query fetches tax rates that satisfy a tax request object with key 1|3|1|DE|79|12345 (store ID = 1, customer tax class ID = 3, product tax class id = 1, country code = DE, region id = 79 "Niedersachsen", and postcode =…

gistfile1.txt

(SELECT 
    `main_table`.`tax_calculation_rate_id`,
    `main_table`.`tax_calculation_rule_id`,
    `main_table`.`customer_tax_class_id`,
    `main_table`.`product_tax_class_id`,
    `rule`.`priority`,
    `rule`.`position`,
    `rate`.`rate` AS `value`,
    `rate`.`tax_country_id`,
    `rate`.`tax_region_id`,
    `rate`.`tax_postcode`,
    `rate`.`tax_calculation_rate_id`,
    `rate`.`code`,
    IF(title_table.value IS NULL,
        rate.code,
        title_table.value) AS `title`
FROM
    `tax_calculation` AS `main_table`
        INNER JOIN
    `tax_calculation_rule` AS `rule` ON `rule`.`tax_calculation_rule_id` = main_table.tax_calculation_rule_id
        INNER JOIN
    `tax_calculation_rate` AS `rate` ON rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id
        LEFT JOIN
    `tax_calculation_rate_title` AS `title_table` ON rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id
        AND title_table.store_id = '1'
WHERE
    (customer_tax_class_id = 3)
        AND (product_tax_class_id IN ('1'))
        AND (rate.tax_country_id = 'DE')
        AND (rate.tax_region_id IN (0 , 79))
        AND (rate.zip_is_range IS NULL)
        AND (rate.tax_postcode IS NULL
        OR rate.tax_postcode IN ('*' , '',
        '12345',
        '12345*',
        '1234*',
        '123*',
        '12*',
        '1*'))) UNION (SELECT 
    `main_table`.`tax_calculation_rate_id`,
    `main_table`.`tax_calculation_rule_id`,
    `main_table`.`customer_tax_class_id`,
    `main_table`.`product_tax_class_id`,
    `rule`.`priority`,
    `rule`.`position`,
    `rate`.`rate` AS `value`,
    `rate`.`tax_country_id`,
    `rate`.`tax_region_id`,
    `rate`.`tax_postcode`,
    `rate`.`tax_calculation_rate_id`,
    `rate`.`code`,
    IF(title_table.value IS NULL,
        rate.code,
        title_table.value) AS `title`
FROM
    `tax_calculation` AS `main_table`
        INNER JOIN
    `tax_calculation_rule` AS `rule` ON `rule`.`tax_calculation_rule_id` = main_table.tax_calculation_rule_id
        INNER JOIN
    `tax_calculation_rate` AS `rate` ON rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id
        LEFT JOIN
    `tax_calculation_rate_title` AS `title_table` ON rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id
        AND title_table.store_id = '1'
WHERE
    (customer_tax_class_id = 3)
        AND (product_tax_class_id IN ('1'))
        AND (rate.tax_country_id = 'DE')
        AND (rate.tax_region_id IN (0 , 79))
        AND (rate.zip_is_range IS NOT NULL)
        AND ('12345' BETWEEN rate.zip_from AND rate.zip_to)) ORDER BY `priority` ASC , `tax_calculation_rule_id` ASC , `tax_country_id` DESC , `tax_region_id` DESC , `tax_postcode` DESC , `value` DESC