CODEWITHSUNDEEP
mysqljoin
George
George

Reputation: 901

SQL three table join help - customers without orders in dates

I have the (drastically simplified) schema of three tables as follows:

enter image description here

I'm looking to query the database for customers without orders in a certain date range. I know LEFT JOIN can be used to simply find customers with no invoices etc., but I am unsure how I can leverage JOIN (or perhaps even OUTER JOIN) to introduce a date range.

Is this possible using SQL JOIN or should I be looking to use NOT IN syntax to find customers that aren't in a given result set?

My database software is MySQL.

Upvotes: 0

Views: 111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Try this:

select c.*
from customers c left join
     invoices i
     on c.code = i.customer_code and
        i.date between DATE1 and DATE2
where i.customer_code is null;

The key idea is to put the date range in the on clause.

Upvotes: 1

Related Questions