×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

combining queries/subqueries

combining queries/subqueries

combining queries/subqueries

(OP)
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.

CODE --> 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.

CODE --> 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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close