Postgres: How to join tables when every table can be empty?

user_10455357 0
Viewed 204

I have 3 tables. And I use math operations for this three tables.

SELECT table_1.a + table_2.a + table_3.a
FROM table_1
LEFT JOIN table_2 ON table_2.user_id = table_1.user_id
LEFT JOIN table_3 ON table_3.user_id = table_1.user_id
WHERE table_1.user_id = ?

But every table from this query can be empty.

How I can fix to work correctly with empty tables?

Thanks.

postgresql
2 Answers

Example:

SELECT * FROM table_1.a T1
LEFT JOIN table_2 T2 ON T1.user_Id = T2.FK

FK is your foreign key on the second table. A Left Join will return all rows from table1 even if they don't exist in table2.

user_74042130

One easy way would be to LEFT JOIN against a row that contains your target id. Then (if so desired) use coalesce() to replace the null values and get a non null result where rows are missing.

WITH target AS (SELECT ? AS user_id)
SELECT coalesce(table_1.a,0) 
       + coalesce(table_2.a,0) 
       + coalesce(table_3.a,0) AS sum_a
FROM target
LEFT JOIN table_1 ON target.user_id = table_1.user_id
LEFT JOIN table_2 ON target.user_id = table_2.user_id
LEFT JOIN table_3 ON target.user_id = table_3.user_id
;

You could as well start with users_table.user_id or some such where user_id is the primary key and you know the user_id must exist (You probably have FK relationships here?). And filter using a WHERE clause.

SELECT coalesce(table_1.a,0) 
       + coalesce(table_2.a,0) 
       + coalesce(table_3.a,0) AS sum_a
FROM user_table u
LEFT JOIN table_1 ON u.user_id = table_1.user_id
LEFT JOIN table_2 ON u.user_id = table_2.user_id
LEFT JOIN table_3 ON u.user_id = table_3.user_id
WHERE u.user_id = ?
;
user_106678390
Related