CODEWITHSUNDEEP
sqlreturnrowdefaultdefault-value
Johny19
Johny19

Reputation: 5582

SQL return a default value if a row is not found [PostgreSQL]

I'm wondering if it was doable (in one query if possible) to make the query return a default value if a row is missing ? For example takes these 2 tables and given my query takes 2 parameter (place_id and user_id)

T1
place_id / tag_id
1            2
1            3
1            4
2            4
3            2
4            5

T2
user_id / tag_id / count
100         2        1
100         3        20
200         4        30
200         2         2
300         5        22

As you see, the pair user/tag (100,4) is missing. What I would like to archive is a query that will return me these 3 results

tag_id / count
2           1
3           20
4           0

I know that i can do this with something like this but it doesn't really match the final result as it only works if i know in advance the tag_id... and obviously only return 1 row..:

SELECT T1.tag_id, T2.count 
from T1 t1 
    left join T2 t2 on t1.tagId=t2.tag_id 
where t1.place_id=1 
UNION ALL 
select tag_id,0 
from T1 
where not exist (select 1 from T2 where user_id=100 and tag_id=4) 
  and tag_id=4;

EDIT: My question was not complete and had missing cases here is an example (curtesy of @a_horse_with_no_name) http://sqlfiddle.com/#!12/67042/4

Thank you!

Upvotes: 3

Views: 3541

Answers (1)

user330315
user330315

Reputation:

The outer join will already take care of what you want.

As t1 is the "left table" of the join, all rows from t1 will be returned. Columns from the "right table" (t2 in your example) will then have a null value. So you only need to convert that null to a 0:

select t1.tag_id, coalesce(t2.cnt, 0)
from T1 t1 
    left join T2 t2 on t1.tag_Id=t2.tag_id 
and t1.place_id = 1;

SQLFiddle example: http://sqlfiddle.com/#!12/ed7bf/1

Unrelated but:

Using count as a column name is a really bad idea, because it will require you to always enclose the column name in double quotes: t2."count" because it is a reserved word. Plus it doesn't really document the purpose of the column. You should find a better name for that.

Upvotes: 1

Related Questions