Databases
StackOverflow2013
Postsdbo
POhow can I optimise and fix this double JOINED MySql query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow can I optimise and fix this double JOINED MySql query?
    primarykey
    data
    text
    <p>I'm trying to optimise a query by doing it all in one command and using Mysql's power instead of using CPU power in getting data in more Sql commands and ordering stuff with bubble_sort or things like that.</p> <p>I have 3 tables which I'm using in this query :</p> <ol> <li>products (produse p)</li> <li>products cross reference (produse_xref p_xref)</li> <li>internal stock infos (stoc_intern s_i)</li> </ol> <p><strong>table produse</strong></p> <pre><code>id | product_sku | product_name | product_desc | material | image_link 22 | AP8744433-01 | Red pen | Red pen with gold cap | wood | 18923.jpg 23 | AP8744433-02 | Blue pen | Blue pen with gold cap | wood | 18923.jpg 23 | AP8744434-01 | Blue pen | Blue pen with gold cap | wood | 18923.jpg </code></pre> <p><strong>table produse_xref</strong></p> <pre><code>id | category_id | product_id (checks with id from table produse) 1 | 157 | 22 2 | 157 | 23 3 | 49 | 140 </code></pre> <p><strong>table stoc_intern</strong></p> <pre><code>id | p_sku | final_stock (stoc_final) 400 | AP8744434-01 | 400 401 | AP8744433-01 | 500 402 | AP8744433-02 | 300 403 | AP8744433-02 | 205 </code></pre> <p>I need to display my products, 9 at a time on a page, grouped by ROOT product_sku (AP8744433), and displaying SUM or total of items in stock per each group.</p> <p>A group is for example in the sku code : AP8744433-01 and -02, will display only first one, -01 (with all subsequent items included -02, -03, etc) but display SUM of stock items for the whole group, in descending order, from the internal stock.</p> <p>And...tada...here's the answer! :) I'm sorry that you guys notched down my post by -2 and left it like that so people would ignore it as spam or so...I managed to find the answer to my question on my own.</p> <p>Here's the non working query below :</p> <pre><code>$query_products = "SELECT p.*, s_i.cod_website, SUM(s_i.stoc_final) FROM (produse_xref p_xref RIGHT JOIN produse p ON p.id=p_xref.product_id AND p_xref.categorii_id=".$_GET['id'].") INNER JOIN stoc_intern s_i GROUP by s_i.cod_website ORDER BY SUM(s_i.cod_website) LIMIT 9"; </code></pre> <p>And here's the <strong>good version</strong> : </p> <pre><code>$query_products = "SELECT p.*, SUM(s.stoc_final) FROM (produse_xref p_xref INNER JOIN produse p ON p.id=p_xref.product_id AND p_xref.categorii_id=".$_GET['id'].") LEFT JOIN stoc_intern s ON p.product_sku=s.cod_website GROUP BY substr(p.product_sku, 1, 6) ORDER BY SUM(s.stoc_final) DESC LIMIT 12 OFFSET ".$_offset.""; </code></pre> <p>So the valid query does list all my product rows where they fit in the right category and they cross reference with id's from products id's cross-reference, and also the whole row thing is ordered by GROUP SUM desc. Voila!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScaitriona
    1. USAdrian Tanase
    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. POhow can I optimise and fix this double JOINED MySql query?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POhow can I optimise and fix this double JOINED MySql query?
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POhow can I optimise and fix this double JOINED MySql query?
      1. USAdrian Tanase
      1. VTFavorite
    1. COWhen you have a question this long, it is a good idea to provide schema, sample data, and desired output.
      singulars
      1. POhow can I optimise and fix this double JOINED MySql query?
      1. USRedFilter
    2. CObasically I provided how things work about my query, and would like to know if my query is valid, to be short, and if it can provide output
      singulars
      1. POhow can I optimise and fix this double JOINED MySql query?
      1. USAdrian Tanase
    3. COPlease provide data structures. Also, please try to summarize your problem. After 5 minutes reading you, I am still unsure as to where the problem is. Your "query almost works", but please show your expected result vs. the actual result.
      singulars
      1. POhow can I optimise and fix this double JOINED MySql query?
      1. USRandomSeed
 

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