I have two tables, the first is a table that contains status and the second is contains balance. I want to join those two tables but with only specific status on the first table
Sample Data Table 1
+------------------+------------------+--------------+ | appsflyer_id | customer_id | status | +------------------+------------------+--------------+ | AA1 | 123 | NEW | | AA1 | 123 | ACTIVE | | AA1 | 124 | NEW | | AA1 | 124 | ACTIVE | +------------------+------------------+--------------+
Sample Data Table 2
+------------------+--------------+ | customer_id | balance | +------------------+--------------+ | 123 | 100 | | 124 | 250 | +------------------+--------------+
Desired output
+------------------+------------------+--------------+------------------+ | appsflyer_id | customer_id | status | balance | +------------------+------------------+--------------+------------------+ | AA1 | 123 | NEW | (null/zero/empty)| | AA1 | 123 | ACTIVE | 100 | | AA1 | 124 | NEW | (null/zero/empty)| | AA1 | 124 | ACTIVE | 250 | +------------------+------------------+--------------+------------------+
I tried to use:
SELECT appsflyer_id, a.customer_id, status, balance FROM `table_1` a left join `table_2` b on a.customer_id = b.customer_id
It will return multiple balance which is not quite right
+------------------+------------------+--------------+------------------+ | appsflyer_id | customer_id | status | balance | +------------------+------------------+--------------+------------------+ | AA1 | 123 | NEW | 100 | | AA1 | 123 | ACTIVE | 100 | | AA1 | 124 | NEW | 250 | | AA1 | 124 | ACTIVE | 250 | +------------------+------------------+--------------+------------------+
Advertisement
Answer
Looks like you want CASE
SELECT a.appsflyer_id, a.customer_id, a.status,
CASE WHEN a.status = 'ACTIVE' THEN b.balance END balance
FROM `table_1` a
LEFT JOIN `table_2` b on a.customer_id = b.customer_id