Databases
StackOverflow2013
Postsdbo
POoptimizing mysql query with nested subqueries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POoptimizing mysql query with nested subqueries
    primarykey
    data
    text
    <p>To make a long story short, I have some mildly complex queries (see example below) and need to save their results into a table rather than running them every time. I have about 6 months of experience with MYSQL, so I know just enough to get myself into trouble. </p> <p>Background: Each product has multiple rules that determine pricing, shipping, etc. The kitruleoptions table connects each rule to the dropdown options it will apply for -- so if a customer selects "Add optional X," the rules pertaining to Xs get applied. The optionsets table connects each ProductItemID to the dropdown options it is associated with. So, a rule should apply for a particular product item only if all of the product item's dropdown options are found in that rule's set of dropdown options. In other words, it's not that the rule necessarily applies every time X is selected, but rather, it may apply every time X is selected in dropdown A and either Y or Z are selected in dropdown B, but not if W is selected in dropdown B. </p> <p>Here is an example query that gets me the total price for an individual ProductItemID. </p> <pre><code>SELECT ProductItemID, SUM(RulePrice * ItemQuantity) AS Price FROM (SELECT RuleGroups, ProductItemGroups, RulePrice, ItemQuantity, productitems.ProductItemID FROM kitrules LEFT JOIN productitems ON productitems.ProductID = kitrules.ProductID LEFT JOIN (SELECT RuleID, GROUP_CONCAT(OptionGroupID ORDER BY OptionGroupID) AS RuleGroups FROM kitruleoptions WHERE OptionGroupID IN (SELECT OptionGroupID FROM optionsets WHERE ProductItemID = %s) GROUP BY RuleID) AS k ON k.RuleID = kitrules.RuleID LEFT JOIN (SELECT ProductItemID, GROUP_CONCAT(OptionGroupID ORDER BY OptionGroupID) AS ProductItemGroups FROM optionsets GROUP BY ProductItemID) AS o ON o.ProductItemID = productitems.ProductItemID WHERE productitems.ProductItemID = %s AND RuleGroups = ProductItemGroups) AS x GROUP BY x.ProductItemID </code></pre> <p>Currently, this select (or rather, a more complex one that selects more than just the price) goes inside an INSERT INTO statement somewhat like this:</p> <pre><code>INSERT INTO pricelists (ProductItemID, Price) SELECT [the query above] WHERE ProductItemID = %s </code></pre> <p>I am trying to formulate it instead something like this: </p> <pre><code>INSERT INTO pricelists (ProductItemID, Price) SELECT [a new query that only requires ProductID] WHERE ProductItemID IN (SELECT ProductItemID FROM productitems WHERE ProductID = %s) </code></pre> <p>... and only need to input ProductID in order to update the entire product at once. That way, I would no longer have to loop through a PHP array to do it. ProductID is found in both the kitrules table and in the productitems table, so I feel like there should be a way, but I can't seem to find it. So my questions are, how do I reformulate the query to only need ProductID? Is it even possible? Is there a way to directly compare the set of optionsets and the set of kitruleoptions without the GROUP_CONCAT thing? Also, how would I set this up with a trigger, and what should it trigger on? I've never done triggers before. </p> <p>EDIT: Here is my <a href="http://sqlfiddle.com/#!2/d55e9" rel="nofollow">SQL Fiddle</a> which has a simple example of how the tables are supposed to end up. The idea is that I started out with a setup where each product simply had one or more items, but then due to a late change in requirements (yay), I had to add in the ability to generate items that are kits, or combinations of multiple different items, and yet the kits are items themselves. The requirement is that a kit's ItemCost is the sum of its contained items' ItemCosts, and a kit's Price is the sum of its contained rules' RulePrices times ItemQuantity, and its shipping is determined by the combined shipping of its contained items' shipping information, but otherwise, a kit is just another item. Cue three weeks of headache and counting. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmiyasudokoro
    1. USmiyasudokoro
    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. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POoptimizing mysql query with nested subqueries
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf you want help, could you reformat the query and provide an alias for every column? It is not possible to follow the query, because we don't know the table structures.
      singulars
      1. POoptimizing mysql query with nested subqueries
      1. USGordon Linoff
    2. COuse js fiddle to create structure and then we will be able to sort out your problem
      singulars
      1. POoptimizing mysql query with nested subqueries
      1. USMuhammad Raheel
    3. CO@raheelshan I think [SQLFiddle](http://sqlfiddle.com/) might be more appropriate :)
      singulars
      1. POoptimizing mysql query with nested subqueries
      1. USGarethD
 

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