Select_Sale_Items Only Returning 'Other'

[AndrewT]

EDIT: FIXED A few things were wrong but I basically took the code and wrote it out word for word using my own tables and table abbreviations, and got it working.

 

I am trying to figure out why the "select_sale_items" function is only returning items with the product_category of 'other'. To be clear, it returns only prodcuts with a product_category of 'other' no matter whether it is set to 'true' or 'false'. I have posted my SQL code below. I have changed a few of the table names, and instead of "coffee" the database keeps track of another product, so where

CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole)

would be, I simply have

s.size

 

Hopefully someone will be able to tell me what I am doing wrong.

 

DELIMITER $$
CREATE PROCEDURE select_sale_items (get_all BOOLEAN)
BEGIN
IF get_all = 1 THEN
SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name, ncp.price, ncp.stock, ncp.description FROM sales AS sa INNER JOIN soap_products AS ncp ON sa.product_id=ncp.id INNER JOIN soap_categories AS ncc ON ncc.id=ncp.soap_category_id WHERE sa.product_type="other" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) )
UNION SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, s.size, sc.price, sc.stock, gc.description FROM sales AS sa INNER JOIN specific_products AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_products AS gc ON gc.id=sc.general_products_id WHERE sa.product_type="coffee" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) );
ELSE
(SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name FROM sales AS sa INNER JOIN soap_products AS ncp ON sa.product_id=ncp.id INNER JOIN soap_categories AS ncc ON ncc.id=ncp.soap_category_id WHERE sa.product_type="other" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2) UNION (SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, s.size FROM sales AS sa INNER JOIN specific_products AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_products AS gc ON gc.id=sc.general_products_id WHERE sa.product_type="coffee" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2);
END IF;
END$$
DELIMITER ;

 

EDIT:

I have realized that, due to my table structure, 'other' and 'coffee' should be switched. I have adapted the tables for a different product. I was also wondering why it was displaying the sale price for the 'other' products, but the info and image of the 'coffee' product. But I believe that is because I haven't switched the 'other' and 'coffee' like I said. I have class, but I will try that when I get home. However, I still have the original problem above.

[Larry]

Glad it's working, kudos for figuring it out, and thanks for letting us know.