MySQL subquery in select with link to outer field

Question

I convert an old software (that use MS-ACCESS MDB) to mySQL. I have a query that takes long time to run (actualy I break running after 5 minutes of waiting) How can I write it?

SELECT pa_ID, pa_PRODUCT_ID, pr_ID,pr_NAME,Sum(pa_KILOS) as IN_KILOS, 
    (select sum(pl_KILOS) from POLHSH where POLHSH.pl_PRODUCT_ID = pa_PRODUCT_ID and POLHSH.pl_PARALABH_ID = pa_ID) as OUT_KILOS From PARALABH, PRODUCTS WHERE pa_company_id=1 GROUP BY pa_ID, pa_PRODUCT_ID,pr_ID, pr_NAME HAVING pa_ID=241 and pr_id=pa_PRODUCT_ID

Thanks in advance

Can you deliver the table definition for the three tables? Its hard to think into the dark... — Myonara, Feb 15 '18 at 18:20
CREATE TABLE `polhsh` ( `pl_ID` INT(11), `pl_AA` INT(11), `pl_PARALABH_ID` INT(11), `pl_PRODUCT_ID` INT(11), `pl_KILOS` DOUBLE, `pl_COMPAY_ID` INT(11), PRIMARY KEY (`pl_ID`, `pl_AA`), ) ;CREATE TABLE `paralabh` ( `pa_ID` INT(11), `pa_AA` INT(11), `pa_PRODUCT_ID` INT(11), `pa_KILOS` DOUBLE, `pa_COMPANY_ID` INT(11), PRIMARY KEY (`pa_ID`, `pa_AA`); and CREATE TABLE `products` ( `pr_ID` INT(11), `pr_NAME` VARCHAR(50), PRIMARY KEY (`pr_ID`) ); ***how can add text formated like code in comments??*** — Elias Giannopoulos, Feb 15 '18 at 18:43
you can edit your post then you have formatting options available. — Myonara, Feb 15 '18 at 19:46
Use a JOIN clause in both SELECT statements. Without JOIN clause you have Cartesian relationship (every record associates with every record of other table). This type of query can be very slow with large dataset. — June7, Feb 15 '18 at 20:52
@June7 ... OP is not running a cartesian join but an implicit join (the pre-ANSI-92 version before `JOIN` was introduced). See last `WHERE` condition. — Parfait, Feb 15 '18 at 22:54

score 1 · Accepted Answer · answered Feb 15 '18 at 23:09

Consider avoiding the correlated subquery which runs a SUM separately for each row and use a join of two aggregate queries each of which runs SUM once by grouping fields. Additionally, use explicit joins, the current SQL standard in joining tables/views.

Please adjust column aliases and names to actuals as assumptions were made below.

SELECT t1.*, t2.OUT_KILOS
FROM
  (SELECT pa.pa_ID,
          pa.pa_PRODUCT_ID,
          pr.pr_ID,
          pr.pr_NAME,
          SUM(pa.pa_KILOS) AS IN_KILOS
   FROM PARALABH pa
   INNER JOIN PRODUCTS pr
      ON pr.pr_id = pa.pa_PRODUCT_ID
   WHERE pa.pa_company_id = 1
   GROUP BY pa.pa_ID,
            pa.pa_PRODUCT_ID,
            pr.pr_ID,
            pr.pr_NAME
   HAVING pa.pa_ID = 241
  ) AS t1

INNER JOIN
  (SELECT POLHSH.pl_PRODUCT_ID,
          POLHSH.pl_PARALABH_ID 
          SUM(pl_KILOS) As OUT_KILOS
   FROM POLHSH
   GROUP BY POLHSH.pl_PRODUCT_ID,
            POLHSH.pl_PARALABH_ID 
  ) AS t2

ON t2.pl_PRODUCT_ID = t1.pa_PRODUCT_ID
AND t2.pl_PARALABH_ID = t1.pa_ID

MySQL subquery in select with link to outer field

1 Answers1