Maggie Liu
Maggie Liu

Reputation: 366

FULL OUTER JOIN/COALESCE isn't populating properly

I have no idea why my coalesce function isn't working.

I have two tables that I'm drawing from.

Table 1 and Table 2 Table 1 price is 0. Table 2 price is 0.33. If I coalesce I should get the 33 cents.

I do a FULL OUTER JOIN on both tables. I run the following query with the coalesce in the select statement.

SELECT 
p.item_id, 
p.date, 
B1.price as backup_1_price,
B2.price as backup_2_price,
      , COALESCE(B1.backup_1_price, 
                B2.backup_2_price)          as coalesce_price         

FROM 
primary_prices AS p 
    FULL OUTER JOIN backup_prices_1 AS b1
      ON p.item_id = b1.item_id
    FULL OUTER JOIN backup_prices AS b2
      ON p.item_id = b2.item_id
    
WHERE 
    1=1 
    AND p.item_id = '1'

Result when I coalesce:

item_id date backup_1_price backup_2_price coalesce_price
1 2022-02-28 0 0.33 0

I'm not sure why my coalesce isn't working here? Thanks so much in a advance!

Upvotes: 1

Views: 920

Answers (0)

Related Questions