What type of JOIN to use? mysql

Question

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 1

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.

Answer 2

Try this :

SELECT 
    product.product_id
FROM product 
    LEFT JOIN
        (
            SELECT 
                product.product_id
            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
        ) AS psold
            ON product.product_id = psold.product_id
WHERE psold.product_id IS NULL

With this, you have a subrequest with products sold and you join it with all products, the WHERE clause selects only products that are in the first table (all products) and not in the second (sold products).