Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This query will work, to <code>SELECT</code> more columns from the products table add them to both the <code>SELECT</code> and <code>GROUP BY</code> clauses:</p> <pre><code>SELECT p.product_id ,SUM(IFNULL(ps.product_quantity,0)) AS product_quantity_sold ,SUM(IFNULL(ps.product_total_price,0)) AS total_price_sold FROM product p LEFT JOIN product_sold ps ON p.product_id = ps.product_id LEFT JOIN sales s ON ps.product_sales_id = s.sales_id WHERE p.product_subcategory_id = $subcategory_id AND ( s.sales_id IS NULL OR ( s.sales_approved = '1' AND s.sales_approved_time &gt; '$start_timestamp' AND s.sales_approved_time &lt; '$end_timestamp' ) ) GROUP BY p.product_id ORDER BY SUM(IFNULL(ps.product_quantity,0)) DESC </code></pre> <p><strong>Explanation</strong></p> <p>This query would have been much simpler if you could have used <code>product_sold_approved</code> and <code>product_sold_approved_time</code> in the <code>WHERE</code> clause instead of values from the sales table.</p> <p>You <code>LEFT JOIN product_sold</code>, a <code>LEFT JOIN</code> means you keep <strong>all</strong> records from the <code>products</code> table, and those that have been sold will get joined to each of the <code>product_sold</code> records. Then you do the same for the <code>sales</code> table.</p> <p>So at this stage, you have lots of rows that are <code>product + product_sold + sales</code> but you also have all the unsold products <code>product + NULL + NULL</code>. You need to filter out all the joined records where the matching sale fields do not meet your criteria, but you need to leave all the records that failed to join alone.</p> <p>To achieve this you have a WHERE clause that deals with each set of records separately. <code>WHERE (condition A) OR (condition B)</code>.</p> <p>Condition A deals with our unsold products, <code>WHERE s.sales_id IS NULL</code> - all records that couldn't join to a sale are included in the result set and don't have to match the other criteria.</p> <p><code>OR (sales_approved = 1 AND ... AND ...)</code> records where s.sales_id isn't NULL will have to pass this half of the where clause, hence we filter out unwanted sales.</p> <p>At the end we're left with a result set that contains all records that didn't have any sales + all product/product_sales/sales record that met your criteria. Then we can just GROUP BY product_id AND SUM what's left.</p> <p>I have <code>IFNULL</code>s in my SQL, these are because if you're summing lots of values and some of them might be <code>NULL</code>, if a single value is <code>NULL</code> then the result of the <code>SUM</code> is <code>NULL</code>. The <code>IFNULL(column,0)</code> protects against this by converting any <code>NULL</code> values to a <code>0</code>. <code>1 + NULL = NULL</code>, <code>1 + 0 = 1</code>. <strong>HOWEVER</strong>, on reflection I thats probably not needed for the query - remove them and you should notice the change.</p>
    singulars
    1. This table or related slice is empty.
    1. POTrouble with LEFT OUTER JOIN
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USOGHaza
    1. USOGHaza
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POTrouble with LEFT OUTER JOIN
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf it works, I'll explain it
      singulars
      1. PO
      1. USOGHaza
    2. COIt did work perfectly! Please explain it to understand more, thanks!!
      singulars
      1. PO
      1. USbmacuer
    3. CONo problem, unnecessarily long explanation added.
      singulars
      1. PO
      1. USOGHaza
 

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