How to get value of another row if row does not exist in table?

Question

I have two tables named as tbl_brands and tbl_counter_stock. In tbl_brands there is key-value pair of brand id and brand name. In tbl_counter_stock columns are like brnd_id,2000ml,1000ml,stock,stock_date. Each row represents the brand of liqueur with different stock like O-Opening, R-Received, S-Sales, C-Closing I want to fetch the record for Closing Stock of each brand from this table and if the entry for C (Closing Stock) is not present in this table then I want to fetch O(Opening Stock) of that brand. I am getting both values of Opening and Closing stock if Closing stock is already present for that brand. And this is not a desired result for me. I want to get O(Opening stock) only if C(Closing stock) is not present. Thanks in advance.

    SELECT 
        A.brnd_id,A.brnd_name,'2000ml' capacity,COALESCE(B.`2000ml`,0) bottles,
        B.stock,B.stock_date
        FROM tbl_brands A  
        left OUTER JOIN
        tbl_counter_stock AS B
        on A.BRND_ID=B.BRND_ID 
        AND B.STOCK IN('O','C')
        AND B.STOCK_DATE='2017-01-18'

Answer 1

I think the solution is easier to understand when you create a table:
Create a table (table_C) with Closing_Stock only (WHERE CLAUSE reduced to B.STOCK='C') Then Left join from your tbl_brands to your table_C to find all the brand_ids that are missing a closing stock record and join your tbl_counter_stock (WHERE B.STOCK='O') to insert the missing ids.

CREATE TABLE brand_stock_overview (Key brnd_id(brnd_id))
SELECT A.brnd_id,A.brnd_name,'2000ml' capacity,COALESCE(B.`2000ml`,0)  
bottles, B.stock,B.stock_date
FROM tbl_brands A  
LEFT JOIN tbl_counter_stock AS B ON(A.BRND_ID=B.BRND_ID)
WHERE B.STOCK ='C'
AND B.STOCK_DATE='2017-01-18';

INSERT INTO brand_stock_overview
SELECT A.brnd_id,A.brnd_name,'2000ml' capacity,COALESCE(B.`2000ml`,0)    
bottles, B.stock, B.stock_date
FROM tbl_brands A
LEFT JOIN tbl_counter_stock AS B ON(A.BRND_ID=B.BRND_ID)
LEFT JOIN brand_stock_overview C ON(A.BRND_ID=C.BRND_ID)
where B.STOCK='O' AND C.BRND_ID IS NULL 
AND B.STOCK_DATE='2017-01-18';