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.
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_id. The question did not mention th foreign key relationship that you are assuming.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 = ?
;
full join?ONas well as theWHEREclauses.