SQL Statement to Get All Customers with No Orders

SQL statement to get all customers with no orders

You may want to use LEFT JOIN and IS NULL:

SELECT     Persons.LastName, Persons.FirstName
FROM       Persons
LEFT JOIN  Orders ON Persons.id = Orders.Person_id
WHERE      Orders.Person_id IS NULL;

The result of a left join always contains all records of the "left" table (Persons), even if the join-condition does not find any matching record in the "right" table (Orders). When there is no match, the columns of the "right" table will NULL in the result set.

How to find all customers who didn't place an order?

SELECT  c.CustomerId
        ,Name
FROM    dbo.Customers c 
            LEFT JOIN dbo.Orders o ON o.CustomerId = c.CustomerId
WHERE   OrderId IS NULL

Mysql query select customers with no orders in each year

You want a list showing customers and years that are not present in the orders table. So get a list of all customers combined with all years and then subtract the customers and years that you find in the orders table:

select o.yr, c.customer_id
from customers c
cross join (select distinct year(order_date) as yr from orders) o
where (c.customer_id, o.yr) not in (select customer_id, year(order_date) from orders);

Show all customers who havent placed an order in a specific time frame

Desired Result - Show the 5 customers who havent placed an order where the shipping depart date is in 2019

Use NOT EXISTS:

SELECT c.* 
FROM Customer c
WHERE NOT EXISTS (SELECT 1
                  FROM CustomerOrder co JOIN 
                       Shipment sh
                       ON co.shipmentID = sh.ShipmentID
                  WHERE c.CustomerID = co.CustomerID AND
                       sh.DepartDate >= '2019-01-01' AND
                       sh.DepartDate < '2020-01-01'
                 );

MySQL query to find customers who have made 0 orders / no orders

How about

SELECT c.*
FROM Customers c LEFT JOIN
Orders o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL

or

SELECT c.*
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)

SQL Statement Help - Select Only Customers with no order for past 3 months

;WITH CTE_LastOrder (CustomerId, LastOrderDate) As
(
SELECT CustomerId, MAX(OrderDate) LastOrderDate
FROM Orders 
GROUP By CustomerId
)
SELECT * from Customers C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > Cast(Floor(Cast(dateAdd(Month,-3, GetDate()) as Float))as DateTime) 

Above is the basic sql for SQL Server. There might be slight difference in the syntax.

How to select customers that didn't place an order in the last 7 days

Try adding this at the and of your query :

having max(orders.order_date) < dateadd(day, -7, getdate())

Select all customers and all orders with single query

Just use a left join between the two tables:

SELECT c.ID, c.NAME, o.id AS OrderId, o.Status AS OrderStatus
FROM Customers c
LEFT JOIN Orders o
    ON o.ClientID = c.ID
ORDER BY c.ID, o.id;

---

Related Topics