CODEWITHSUNDEEP
mysqlsqljoin
Ayrton
Ayrton

Reputation: 2313

How to write a join query with a date value fitting within a range and outside of another range

The problem is basically this: I have a table "client" and a table "service", in which a client may have requested multiple services. Now, I need a query that returns a list of clients that have requested services in one trimester, but not in another.

Example: only the clients which requested services between 2018-04-01 and 2018-06-30, but have not requested services between 2018-01-01 and 2018-03-31.

How do I translate that request into a query?

I have tried using a "not between" in a simple query, but it does not work (regardless of how the "between" is phrased). It simply returns the clients between the second trimester and ignores the "not" clause of the first. I have also tried joining the service table twice, with the same result.

How I have tried that:

SELECT DISTINCT c.id_client
FROM client c
JOIN service s1 on c.id_client = s1.id_client
JOIN service s2 on c.id_client = s2.id_client
WHERE s1.date between '2018-04-01' and '2018-06-30'
AND !(s2.date between '2018-01-01' and '2018-03-31')

That is obviously wrong, and I tried to write the second "between" clause in several different ways as well. The funniest thing is that the opposite works just fine, selecting the clients that have requested services between both ranges. I considered running a "not in" subquery on top of that, but it would probably come back to bite me later.

What is the appropriate way to do this?

Upvotes: 1

Views: 25

Answers (1)

fubar
fubar

Reputation: 17398

This is untested, but I think it should give you the result you're after.

The theory is to start with all clients (FROM client c), and to filter the result set to those clients who have definitely requested services in the second quarter of the year (INNER JOIN service s1).

Then, attempt to find services requested in the first quarter of the year (LEFT JOIN service s2). The LEFT JOIN is important, because not all clients will have requested services in the first quarter. Those that have are then excluded by the WHERE s2.id_service IS NULL.

SELECT c.id_client
FROM client c
INNER JOIN service s1 
    ON c.id_client = s1.id_client
    AND s1.date BETWEEN '2018-04-01' AND '2018-06-30'
LEFT JOIN service s2
    ON c.id_client = s2.id_client
    AND s2.date BETWEEN '2018-01-01' AND '2018-03-31'
WHERE s2.id_service IS NULL;

Upvotes: 1

Related Questions