CODEWITHSUNDEEP
mysqlsqljoin
Steven
Steven

Reputation: 911

SQL Join tables and show default value for unmatched records in the right table

I have 2 tables which I want them to be joined when certain condition has been met.

1) Clients Table

Clients Table

2) Payments Table

Payments Table

With these tables, I want to see how much each client has already paid. If a client has no payments made yet, the amount column should be 0

I had this SQL query but it won't show the clients without payments.

SELECT c.id, c.name, co.amount FROM clients c LEFT OUTER JOIN collectibles co ON c.id = co.client_id WHERE co.removed = 0

The result of this query will only show 1 record and that is client Cliff with payment 25000

How will I be able to achieve the expected output?

Upvotes: 1

Views: 1112

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133390

You should not use left join table's column in where. this way work as inner join in these cases add the condition to the related ON clause 

  SELECT c.id
    , c.name
    , ifnull(co.amount ,0)
FROM clients c 
LEFT OUTER JOIN collectibles co ON c.id = co.client_id 
          AND co.removed = 0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The WHERE clause turns the outer join into an inner join. I think you want:

SELECT c.id, c.name, COALESCE(co.amount, 0) as amount
FROM clients c LEFT OUTER JOIN
     collectibles co
     ON c.id = co.client_id AND co.removed = 0;

You need COALESCE()to return 0 instead of NULL.

Upvotes: 1

Related Questions