Firebird-SQL: showing predefined values instead of nothing

Question

i need to write an SQL query which is similar to the following:

SELECT c.*, p.amount
FROM claims c, payments p
WHERE claims.debit < payments.amount AND c.customerId = p.customerId

Now the problem is, that this query only shows claims, which have a payment which belongs to this claims. But I want it to show every claim and if there's no belonging payment the amount should just be zero.

How can i achieve this?

Btw, I'm doing this in Firebird-SQL.

And sorry for the bad title, i have no idea how to describe it better.

Thank you for your help

Adriano dos Santos Fernandes · Accepted Answer · 2015-02-04 14:59:59Z

4

You should do a LEFT JOIN, but also putting the "<" construction in the JOIN condition.

select c.*,
       coalesce(p.amount, 0) amount
  from claims c
  left outer join payments p
    on c.customerId = p.customerId and
       c.debit < p.amount

If you do like @whirl-mind said, the WHERE will be evaluated against "NULL values" and it will filter out the records.

answered Feb 4, 2015 at 14:59

Adriano dos Santos Fernandes

7557 silver badges21 bronze badges

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

Comments

Whirl Mind · Accepted Answer · 2015-02-04 14:01:13Z

0

You should use a Left Outer Join.

SELECT c.*, p.amount
FROM claims c Left Outer Join  payments p On c.customerId = p.customerId
WHERE claims.debit < payments.amount

Not sure about Firebird-SQL,you should be able to use an IsNull phrase or a Case Statement to fetch 0 if the payment is null.

answered Feb 4, 2015 at 14:01

Whirl Mind

8841 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Firebird-SQL: showing predefined values instead of nothing

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related