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

Question

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!

The row with tag_id = 4 will be returned by the outer join. All you need is to use coalesce() to convert the null value into a 0 See here for an example: sqlfiddle.com/#!12/ed7bf/1 — user330315
– user330315, Commented Nov 24, 2014 at 11:18
@a_horse_with_no_name can we join tables on columns that are not foreign/primary keys ? — user3522371
– user3522371, Commented Nov 24, 2014 at 11:22
@a_horse_with_no_name thank you! I think this does the trick! (btw i need to also add "and t2.user_id=100"; because you can have something like this in T2 also (200,4,50) (an other user linked to the tag 4) — Johny19
– Johny19, Commented Nov 24, 2014 at 11:32

user330315 · Accepted Answer · 2014-11-24 11:38:06Z

1

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.

answered Nov 24, 2014 at 11:38

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Johny19 Over a year ago

About the column name, the table in my question is made up and typed on the fly. I have been trying this query on my tables and i realised that it does not exactly return the result I wanted. Look and this example. sqlfiddle.com/#!12/67042/4 (i have added other tag and users)

user330315 Over a year ago

@Johny19: please edit your question and include the expected output based on your SQLFiddle (include the link to the SQLFiddle in your question).

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related