select conditionally with conditional joins in mysql

Question

I am trying to join two tables, people and sales, and displaying results based on a where condition which should be used to join the tables.

My current attempt is showing only one result but I want all the rows in the people to be shown regardless of whether they have a record in the sales table, if they have a record and the type is credit then only I want to show amount.

CREATE TABLE people(
   person_id INTEGER  NOT NULL PRIMARY KEY 
  ,name  VARCHAR(6) NOT NULL
);
INSERT INTO people(person_id,name) VALUES (1,'John');
INSERT INTO people(person_id,name) VALUES (2,'Alice');
INSERT INTO people(person_id,name) VALUES (3,'Mary');


CREATE TABLE sales(
   sales_id INTEGER  NOT NULL PRIMARY KEY 
  ,customer_id  VARCHAR(6) NOT NULL
  ,type  VARCHAR(6) NOT NULL
  ,amount  VARCHAR(6) NOT NULL
);

INSERT INTO sales(sales_id,customer_id,type,amount) VALUES (1,1,'credit',100);
INSERT INTO sales(sales_id,customer_id,type,amount) VALUES (2,3,'cash',200);

Please check the fiddle example using my current code and help me get the result as shown below:

+---------+--------+
| name    | amount |
+---------+--------+
| john    | 100    |
| alice   | 0      |
| mary    | 0      |
+---------+--------+

score 1 · Accepted Answer · answered Dec 15 '20 at 01:02

That would be:

select p.name, coalesce(s.amount, 0) as amount
from people p
left join sales s on s.customer_id = p.person_id and s.type = 'credit'

The main difference with your original code is that the condition on the left joined table is in the on side of the join rather than in the where clause, so it avoids filtering out people without sales.

Also note the use of table aliases, which shorten the query, and of coalesce() to display 0 instead of null for "missing" amounts.

select conditionally with conditional joins in mysql

1 Answers1