CODEWITHSUNDEEP
mysqlsql
Manse
Manse

Reputation: 38147

Left Join Specific Row

Think this is probably fairly simple but cannot find the correct search terms, so if this is duplicated then great cause im sure there will be an answer somewhere.

I have the following tables setup

CREATE TABLE IF NOT EXISTS `customer` (
  `id` int(6) unsigned auto_increment NOT NULL,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `billing_run` (
  `id` int(6) unsigned auto_increment NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `invoice` (
  `id` int(6) unsigned auto_increment NOT NULL,
  `billing_run_id` int(6) unsigned NOT NULL,
  `customer_id` int(6) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (billing_run_id) REFERENCES billing_run(id),
  FOREIGN KEY (customer_id) REFERENCES customer(id)
) DEFAULT CHARSET=utf8;

with the following data

insert into customer (name) values ('test customer');
insert into billing_run (date) values ('2019-01-01 12:00:00');
insert into billing_run (date) values ('2019-02-01 12:00:00');
insert into billing_run (date) values ('2019-03-01 12:00:00');
insert into invoice (customer_id,billing_run_id) values (1,1);

SQLFiddle here -> http://sqlfiddle.com/#!9/a54162/5

And i want to get the customer records that do not have an invoice related to billing_run with id of 2

My query

select c.id from customer c
left join invoice i on i.customer_id = c.id
left join billing_run br on br.id = i.billing_run_id and br.id = 2
where i.id is null

returns 0 records. Why ?

Upvotes: 0

Views: 72

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You don't need the billing_run table. So I think you intend:

select c.id
from customer c left join
     invoice i
     on i.customer_id = c.id and i.billing_run_id = 2
where i.id is null

Upvotes: 0

Bohica
Bohica

Reputation: 36

You will want to do an exclusive where clause this will return the 1 row that you want.

select * from customer c 
where c.id not in (Select customer_id from invoice i LEFT JOIN billing_run br on 
i.billing_run_id=br.id WHERE br.id=2 and br.id is not null)

http://sqlfiddle.com/#!9/a54162/14

Upvotes: 1

forpas
forpas

Reputation: 164069

First you join the table customer (1 row) with the table invoice (1 row). This join will return 1 row because there is a match between the columns in the ON clause: 

on i.customer_id = c.id

(both i.customer_id and c.id have the value 1 in your sample data).
So there is not any row with i.id is null.
The next join to the table billing_run does not affect the first 2 joined tables.
So the condition:

where i.id is null

returns no rows.
The correct condition (which you had in the original fiddle) is:

where br.id is null

because the join to the table billing_run will return a non matching row for the condition:

on br.id = i.billing_run_id and br.id = 2

because there is no i.billing_run_id = 2 in invoice.

Upvotes: 1

Related Questions