Link to home
Start Free TrialLog in
Avatar of Johnathan Griffiths
Johnathan GriffithsFlag for United States of America

asked on

MariaDB correlated nested subquery

I'm having trouble with a MariaDB nested correlated subquery.  I need to reference a returned rowcount as a column value that is dependent on a customer id from the parent query.  The problem I'm having is MariaDB / MySql has a limitation that only allow correlated queries 1 level deep.  The COUNT portion of the following query violates that rule.  I'm guessing this could be accomplished using JOINs instead, but I'm really at a mental roadblock and could use some fresh eyes.  HELP PLEASE!

SELECT t.*,

#This is the count that I need to have returned as a value
(SELECT COUNT(MERGEDVAL)
FROM (SELECT
CONCAT(sos_deliverydate,
               sos_trip_id,
               sos_route_id,
               sos_routestop) AS MERGEDVAL
        FROM   salesordersummary s2
        WHERE  0 = 0
               AND ( sos_deliverydate BETWEEN '2019-08-18' AND '2019-09-14' )
               AND sos_shipvia <> 'SLS DEL'
               AND ( sos_shipvia <> 'T2'
                      OR Dayofweek(sos_deliverydate) = 1 )
               #this is the referenced value that I need to use.  This works well if I manually fill in a value for 't.sos_customer_id', but fails otherwise
               AND sos_customer_id = 't.sos_customer_id'
        GROUP  BY sos_deliverydate,
                  sos_trip_id,
                  sos_route_id,
                  sos_routestop) AS DROPS) AS DROPCOUNT

FROM (SELECT sos_salesperson_id,
       sos_customer_id,
       Sum(sos_piececount)               AS totalpieces,
       Sum(sos_netinvoicetotalamount)    AS totalnetsales,
       Sum(sos_actualgpamount)           AS totalgp,
       Sum(sos_commissiondropcost)       AS dropcost,
       Sum(sos_commissiontotalpiececost) AS piececost,
       Sum(sos_commissiontotalcost)      AS totaldropcost,
       Sum(sos_commissionamount)         AS commissionamount,
       ( CASE
           WHEN sos_commissionexception = 1 THEN 'PROGRAM'
           ELSE 'STREET'
         end )                           AS customertype,
       ( CASE
           WHEN sos_commissionexception = 1 THEN Sum(sos_actualgpamount)
           ELSE ( Sum(sos_actualgpamount) - Sum(sos_commissiontotalcost) )
         end )                           AS netcommissionprofit,
       sos_commissionexception,
       sos_salespersonname,
       sos_customername,
       '$theStartDate'                   AS startdate,
       '$theEndDate'                     AS enddate,
       '$datepickermin'                  AS themindate,
       '$datepickermax'                  AS themaxdate

FROM salesordersummary
WHERE 0 = 0
       AND ( sos_deliverydate BETWEEN '2019-08-18' AND '2019-09-14' )
       AND sos_shipvia <> 'SLS DEL'
       AND ( sos_shipvia <> 'T2'
              OR Dayofweek(sos_deliverydate) = 1 )
       AND sos_salesperson_id = 'AB1'
GROUP BY sos_salesperson_id,
          sos_salespersonname,
          sos_customer_id,
          sos_customername,
          sos_commissionexception,
          customertype
ORDER BY sos_customername ) AS t
Avatar of Marcus Keustermans
Marcus Keustermans
Flag of South Africa image

Try the following

insert the result sets from the correlated queries into temp tables and then join the temp tables to get your final result.

Something like the code below (dont have mysql on my machine, so couldn't check syntax):

CREATE TEMPORARY TABLE temp1
SELECT  sos_customer_id,CONCAT(sos_deliverydate,
					   sos_trip_id,
					   sos_route_id,
					   sos_routestop) AS MERGEDVAL
FROM   salesordersummary s2
WHERE  0 = 0
	   AND ( sos_deliverydate BETWEEN '2019-08-18' AND '2019-09-14' )
	   AND sos_shipvia <> 'SLS DEL'
	   AND ( sos_shipvia <> 'T2'
			  OR Dayofweek(sos_deliverydate) = 1 )
	   #this is the referenced value that I need to use.  This works well if I manually fill in a value for 't.sos_customer_id', but fails otherwise
	   AND sos_customer_id = 't.sos_customer_id'
GROUP  BY sos_deliverydate,
		  sos_trip_id,
		  sos_route_id,
		  sos_routestop
		  
		  
CREATE TEMPORARY TABLE temp2
SELECT sos_salesperson_id,
	   sos_customer_id,
	   Sum(sos_piececount)               AS totalpieces,
	   Sum(sos_netinvoicetotalamount)    AS totalnetsales,
	   Sum(sos_actualgpamount)           AS totalgp,
	   Sum(sos_commissiondropcost)       AS dropcost,
	   Sum(sos_commissiontotalpiececost) AS piececost,
	   Sum(sos_commissiontotalcost)      AS totaldropcost,
	   Sum(sos_commissionamount)         AS commissionamount,
	   ( CASE
		   WHEN sos_commissionexception = 1 THEN 'PROGRAM'
		   ELSE 'STREET'
		 end )                           AS customertype,
	   ( CASE
		   WHEN sos_commissionexception = 1 THEN Sum(sos_actualgpamount)
		   ELSE ( Sum(sos_actualgpamount) - Sum(sos_commissiontotalcost) )
		 end )                           AS netcommissionprofit,
	   sos_commissionexception,
	   sos_salespersonname,
	   sos_customername,
	   '$theStartDate'                   AS startdate,
	   '$theEndDate'                     AS enddate,
	   '$datepickermin'                  AS themindate,
	   '$datepickermax'                  AS themaxdate

FROM salesordersummary
WHERE 0 = 0
	   AND ( sos_deliverydate BETWEEN '2019-08-18' AND '2019-09-14' )
	   AND sos_shipvia <> 'SLS DEL'
	   AND ( sos_shipvia <> 'T2'
			  OR Dayofweek(sos_deliverydate) = 1 )
	   AND sos_salesperson_id = 'AB1'
GROUP BY sos_salesperson_id,
		  sos_salespersonname,
		  sos_customer_id,
		  sos_customername,
		  sos_commissionexception,
		  customertype

		  
SELECT  COUNT(MERGEDVAL)-- add other columns here
FROM temp1 t1
JOIN temp2 t2 on t1.sos_salesperson_id = t2.sos_salesperson_id
GROUP BY t1.sos_salesperson_id -- add additional grouping columns here

Open in new window

Hi,

Depending on what version of MariaDB you are using.
If the version is  MariaDB 10.2.1. or later I suggest you use CTE syntax and split the query into 3 separate independent sql queries and then join them together. The optimizer takes care of the rest.
CTE is a good way to make complex queries simpler and more manageable and makes temp tables unnecessary although usage of temp-tables could be beneficial when working with large amount of data ( ten or hundreds of millions or more rows).

Regards,
    Tomas Helgi
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.