Johnathan Griffiths
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_netinvoicetotalamo unt) AS totalnetsales,
Sum(sos_actualgpamount) AS totalgp,
Sum(sos_commissiondropcost ) AS dropcost,
Sum(sos_commissiontotalpie cecost) AS piececost,
Sum(sos_commissiontotalcos t) 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_commissiontotalcos t) )
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
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
#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_netinvoicetotalamo
Sum(sos_actualgpamount) AS totalgp,
Sum(sos_commissiondropcost
Sum(sos_commissiontotalpie
Sum(sos_commissiontotalcos
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_commissiontotalcos
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
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
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
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 TRIALMembers 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.
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):
Open in new window