Bryan Gabriel Macuer Bryan Gabriel Macuer - 2 months ago 9
MySQL Question

What type of JOIN to use? mysql

I have a query where all products sold are being selected. What I want to do is reverse the query and get all product not sold within a period of time.

This is the query for all the products sold within a period of time.

SELECT
product.product_id,
product.product_brand_id,
product.product_model_id,
product.product_subcategory_id,
product.product_retail_price,
product.product_wholesale_price,
product.product_aretail_price,
product.product_awholesale_price,
product.product_oretail_price,
product.product_owholesale_price,
product.product_quoted_price,
product.product_aquoted_price,
product.product_dquoted_price,
product_sold.product_type, 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
AND product.product_subcategory_id = '$subcategory_id'
INNER JOIN sales
ON sales.sales_id = product_sold.product_sales_id
WHERE sales.sales_approved = '1'
AND sales.sales_approved_time > '$start_timestamp'
AND sales.sales_approved_time < '$end_timestamp'
GROUP BY product_sold.product_type, product.product_id
ORDER BY SUM(product_sold.product_quantity) DESC


Can someone guide me on how to get unsold products, im kinda stock here with no ideas. Thanks.

ANSWER:

SELECT
p.product_id,
p.product_brand_id,
p.product_model_id,
p.product_subcategory_id,
p.product_retail_price,
p.product_wholesale_price,
p.product_aretail_price,
p.product_awholesale_price,
p.product_oretail_price,
p.product_owholesale_price,
p.product_quoted_price,
p.product_aquoted_price,
p.product_dquoted_price,
pq.product_stock_type
FROM
product p
LEFT JOIN
product_sold ps
ON
p.product_id = ps.product_id
INNER JOIN
product_stock pq
ON
p.product_id = pq.product_id
AND
pq.product_quantity > 0
WHERE
p.product_subcategory_id = '$subcategory_id'
AND
ps.product_id IS NULL

Answer

In general, the way to get members of set A that are not included in set B is to use a left join. I'll start by simplifying your query:

SELECT p.product_id, ps.product_id from product p
LEFT JOIN product_sold ps
WHERE ps.product_id IS NULL

This will return all product records that don't have a value in product_sold. You should be able to add the detail in from there.