Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I don't think your query even does what I think it is supposed to do. You didn't specify what output you expect in your question but I assume you want the SUM() of the qty fields in the two material tables for each of the barcodes in the goods table. (I've also assumed the 'tanggal' field is the same as the date field above it since it isn't in your EXPLAIN output and it means date in english.)</p> <p>You certainly don't need the subqueries in the SELECT statements of the table expressions you're joining to. I also don't see why those need to be table expressions at all. I'm not surprised that query runs incredibly slow. It is correlated subqueries in joined table expressions.</p> <p>I don't think you even need a subquery at all. This is my simplified version:</p> <pre><code>SELECT goods.barcode, SUM(tOUT.qty) AS total_out, SUM(tIN.qty) AS total_in, SUM(tOUT.qty)-SUM(tIN.qty) AS result FROM ( </code></pre> <p>goods LEFT JOIN material_out AS tOUT ON tOUT.barcode=goods.barcode LEFT JOIN material_in AS tIN ON tIN.barcode=goods.barcode WHERE g.category=1 AND tOUT.date BETWEEN '2013-08-15' AND '2013-08-20' AND tIN.date BETWEEN '2013-08-15' AND '2013-08-20' GROUP BY g.barcode</p> <p>There may be a better way to do this but this is how I would quickly put two date ranges together like you're asking:</p> <pre><code>SELECT tNEW.barcode, tNEW.total_out, tNEW.total_in, tNEW.total_out-tNEW.total_in AS result, COALESCE(tOLD.total_out,0) AS total_out_old, COALESCE(tOLD.total_in,0) AS total_in_old FROM ( SELECT g.barcode, COALESCE(SUM(tOUT.qty),0) AS total_out, COALESCE(SUM(tIN.qty),0) AS total_in FROM ( SELECT DISTINCT barcode FROM goods ) AS g LEFT JOIN material_out AS tOUT ON tOUT.barcode=g.barcode LEFT JOIN material_in AS tIN ON tIN.barcode=g.barcode WHERE g.category=1 AND tOUT.date BETWEEN '2013-08-15' AND '2013-08-20' AND tIN.date BETWEEN '2013-08-15' AND '2013-08-20' GROUP BY g.barcode ) AS tNEW LEFT JOIN ( SELECT g.barcode, SUM(tOUT.qty) AS total_out, SUM(tIN.qty) AS total_in FROM ( SELECT DISTINCT barcode FROM goods ) AS g LEFT JOIN material_out AS tOUT ON tOUT.barcode=g.barcode LEFT JOIN material_in AS tIN ON tIN.barcode=g.barcode WHERE g.category=1 AND tOUT.date BETWEEN '2013-01-01' AND '2013-04-31' AND tIN.date BETWEEN '2013-01-01' AND '2013-04-31' GROUP BY g.barcode ) AS tOLD ON tOLD.barcode=tNEW.barcode </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POhow to make a simple subquery in mysql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMike D.
    1. USMike D.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwowww Amazing... You have helped me. you truly simple query and it only took 0.2 seconds
      singulars
      1. PO
      1. USSunarto
    2. COI want to ask again, what if the case I want to display the results in the period. **for example: material_out from the date '2013-08-15 'AND '2013-08-20' was added to the period 1 from 1 January to 31 April. but the results will be shown with different columns**
      singulars
      1. PO
      1. USSunarto
    3. COI'm glad this worked for you. The original query would have been running each of the subqueries in your joins for every row in the goods table (because of the correlated subqueries). That is why it was so slow. Row By Agonizing Row.
      singulars
      1. PO
      1. USMike D.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload