Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

combining queries/subqueries

Status
Not open for further replies.

C_C_K

MIS
Dec 14, 2018
10
US
I have two queries I'd like to combine - and the json is kicking my butt.

The json extract below pulls a sku out of a lookup table along with the quantity ordered {"9999":2} It works fine.

If I add the 3 commented out portions, I get no results.
SQL:
SELECT 
    `p`.`id`,
    `p`.`sku`,
    (SELECT 
            SUM(JSON_EXTRACT(`value`,
                        CONCAT('$.', JSON_QUOTE(`p`.`sku`))))
        FROM
            `base_order_lookups`
        WHERE
            `key` = 'original_skus') `total_qty_ordered`
FROM
    `base_products` AS `p`
#join `base_order_lineitems` `boli` on `boli`.`product_sku` = `p`.`sku`
#join `base_orders` `bo` on `bo`.`id` = `boli`.`base_order_id`
WHERE
	`p`.`type` = 'kit' #and date(`bo`.`order_date`) between '2019-03-27' and '2019-04-02'


Then I'd like to combine it with this query, to get order counts as well as product qty ordered counts.

SQL:
select 
MAX(CASE
        WHEN `key`= 'kit_sku' THEN `value`
    END) 'Package',
    count(`boli`.`base_order_id`) as 'Order_count', sum(`boli`.`final_sale_price` + `boli`.`shipping_charge`) as 'Order total less tax'
   
from `base_order_lineitems` `boli`
join `base_order_lineitem_lookups` `bolil` on bolil.base_order_lineitem_id=boli.id
join `base_orders` bo on `bo`.`id` = `boli`.`base_order_id`
where date(`bo`.`order_date`) between '2019-03-27' and '2019-04-30'
group by `bolil`.`value`


Don't have a whole lot of experience with the subqueries, so that and the json are what I am trying to surmount.

I'm sure there will be questions. I tend to always offer up MORE info than necessary, which tends to confuse people. When these orders are pulled from an external source, the skus that are being stored in the lookup tables are "kit" skus, when we process those orders the first thing we do, is explode the component skus into the base order lineitems (boli) table - so those skus in the lookup tables are only in the lookup tables, they don't get into the "boli" table, and the component skus break out into two or more individual skus requiring varying quantities to make up the kit. The qty ordered in the json in the base order lookups table applies to the KIT skus only, and is the qty I want associated with my calcuated order qty. If these were handled normally it would not be any issue. So hopefully that isn't too confusing to start out with.

any help would be greatly appreciated! TIA!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top