-1

SQL QUERY:

    SELECT 

         c.id AS c_id,c.image AS category_image,b.id AS p_id,b.image AS product_image,
         a.id as product_id,0 AS sc_id,0 AS sub_category,0 AS is_active2, a.*,b.*,c.*

     FROM stock_50644 a 

   JOIN product b ON a.product_id = b.id 

   JOIN category c ON b.category = c.id

     WHERE b.category=1 ORDER BY p_id ASC

If stock_50644 has no product entry then i am getting empty result. How can i make it return all product with stock value null if stock is empty

EDIT:

PRODUCT                                  ; STOCK_50644
  id product_name        category_id     ;    id   product_id               price
---- -------- ---------- --------------- ;  ---- --------- ----- --------------
   1 name1                             1 ;     1   2                           15
   2 name2                             2 ;

if i put WHERE b.id=1 in below query im getting correct expected output.

But as soon as i replace it by ORDER BY b.id ASC LIMIT 1; it is taking forever time and then #2013 - Lost connection to MySQL server during query

SELECT 

    c.id AS c_id, c.image AS category_image, b.id AS p_id, b.image AS product_image, 
    a.id AS product_id, 0 AS sc_id, 0 AS sub_category, 0 AS is_active2, a.*,b.*,c.* 

FROM stock_50644 a 
RIGHT JOIN product b ON a.product_id = b.id AND b.category = 1 
LEFT JOIN category c ON b.category = c.id 
WHERE b.id=1

ORDER BY b.id ASC LIMIT 1;

Ankit Chaurasia
  • 155
  • 8
  • try to provide small tables of queried tables and result table you want. this might help me understand you better. i think you need to right join product and and left join category but need a sample table to check this. – T Olaleye Dec 15 '21 at 08:42
  • @TOlaleye Done. I have edited my question please take a look – Ankit Chaurasia Dec 15 '21 at 09:16

1 Answers1

0

Move the restriction on the category from the WHERE clause to the ON clause of the join:

SELECT 
    c.id AS c_id, c.image AS category_image, b.id AS p_id, b.image AS product_image,
    a.id AS product_id, 0 AS sc_id, 0 AS sub_category, 0 AS is_active2,
    a.*,b.*,c.*
FROM product b
LEFT JOIN stock_50644 a 
    ON a.product_id = b.id AND b.category = 1
LEFT JOIN category c
    ON b.category = c.id
ORDER BY p_id;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360