How to make a connection that removes values?

Question

Customers Holidays

id | name customer_id | start | end
--- + ------ ------------ + -------- + ------
 1 | Peter 1 | 5 | 10
 2 | Simon 1 | 15 | twenty
 3 | Mary 2 | 5 | twenty

What working SQL query gives me all clients without holidays on a specific date? For instance. date = 12

• Peter
• Mary

Is it even manageable with a simple SQL join or do I need to use subqueries?

Answer 1

First, create a query that finds the opposite of what you want: customers who have a vacation on that specific date:

SELECT DISTINCT name
FROM Customers
JOIN Holidays
ON id = customer_id
WHERE start <= 12 AND end >= 12

Result:

Simon

, :

SELECT name
FROM Customers
LEFT JOIN (
    SELECT DISTINCT id
    FROM Customers
    JOIN Holidays
    ON id = customer_id
    WHERE start <= 12 AND end >= 12
) AS T1
ON Customers.id = T1.id
WHERE T1.id IS NULL

:

Peter
Mary

, JOIN . NOT EXISTS, NOT IN EXCEPT. , JOIN, .

, ( ):

CREATE TABLE Holidays (customer_id INT NOT NULL, start INT NOT NULL, end INT NOT NULL);
INSERT INTO Holidays (customer_id, start, end) VALUES
(1, 5, 10),
(1, 15, 20),
(2, 5, 20);

CREATE TABLE Customers (id INT NOT NULL, name NVARCHAR(100) NOT NULL);
INSERT INTO Customers (id, name) VALUES
(1, 'Peter'),
(2, 'Simon'),
(3, 'Mary');

Answer 2

, :

declare @myStart int
declare @myEnd int
SET @myStart = 11
SET @myEnd = 14

SELECT c.name
FROM Customers c
INNER JOIN Holidays h
    ON c.id=h.customer_id
WHERE
(@myStart BETWEEN h.start AND h.end) OR
(@myEnd BETWEEN h.start AND h.end) OR
(@myStart < h.start AND @myEnd > h.end)

Answer 3

  -- Get customers only from Customers table
  SELECT name FROM Customers 
  WHERE id NOT IN ( -- Get the intersection between the two tables
                    SELECT id FROM Customers JOIN 
                    Holidays ON Customers.Id = Holidays.customer_id
                    WHERE Holidays.start=12 AND Holidays.end = 12)

Answer 4

, "" , , / , ?

SELECT
    Name
FROM
    Customers c
    JOIN Holidays h on h.Customer_id = c.ID 
WHERE
    Start >= @Start
    OR End <= @End

Answer 5

select
    c.name
from
    Customers c
    left outer join
        Holidays h
    on
        h.customer_id=c.id
where
    h.start <= 11
    and
    h.end >= 14
group by
    c.id