Databases
StackOverflow2013
Postsdbo
POTrouble with LEFT OUTER JOIN
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble with LEFT OUTER JOIN
    text
    copied!<p>I'm trying to get a list of products order by the amount sold and by date. I also want to display the products that haven't been sold in the list so I tried doing a subquery first but MYSQL is giving me this message:</p> <p>Operand should contain 1 column(s)</p> <pre><code>SELECT product.product_id, product.product_brand_id, product.product_model_id, product.product_subcategory_id, product.product_retail_price, product.product_wholesale_price FROM product WHERE product.product_subcategory_id = $subcategory_id AND (SELECT SUM(product_sold.product_quantity) AS product_quantity_sold, SUM(product_sold.product_total_price) AS total_price_sold FROM product INNER JOIN product_sold ON product.product_id = product_sold.product_id INNER JOIN sales ON sales.sales_id = product_sold.product_sales_id WHERE sales.sales_approved = '1' AND sales.sales_approved_time &gt; '$start_timestamp' AND sales.sales_approved_time &lt; '$end_timestamp') </code></pre> <p>The subquery did not work, So i tried using <code>LEFT OUTER JOIN</code> as suggested by another member with this query:</p> <pre><code>SELECT product.product_id, product.product_brand_id, product.product_model_id, product.product_subcategory_id, product.product_retail_price, product.product_wholesale_price, SUM(product_sold.product_quantity) AS product_quantity_sold, SUM(product_sold.product_total_price) AS total_price_sold FROM product LEFT OUTER JOIN product_sold ON product.product_id = product_sold.product_id AND product.product_subcategory_id = $subcategory_id LEFT OUTER JOIN sales ON sales.sales_id = product_sold.product_sales_id WHERE sales.sales_approved = '1' AND sales.sales_approved_time &gt; '$start_timestamp' AND sales.sales_approved_time &lt; '$end_timestamp' GROUP BY product.product_id ORDER BY SUM(product_sold.product_quantity) DESC </code></pre> <p>But this query with <code>LEFT OUTER JOIN</code> is giving me the list of product sold only, what I want is to also show the products that haven't been sold in the list.</p> <p>Here is the schema used sqlfiddle.com/#!2/967ee</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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