I have the following query:
SELECT c.customer_id
FROM order o
JOIN customer c USING (customer_id)
WHERE c.time >= '2021-01-01 10:00:00' AND c.time < '2021-01-01 11:00:00'
AND (0 IN (22) OR o.product_id IN (22))
LIMIT 1
As long as there is at least one match, one row will be returned, however if there is no match, then there will not be any rows.
Is it possible to return one row with null value if there is no match?
One solution is a LEFT JOIN, but starting with the customers table. However, it is a little complicated:
SELECT o.customer_id
FROM customer c LEFT JOIN
order o
ON c.customer_id = o.customer_id AND
o.time >= '2021-01-01 10:00:00' AND
o.time < '2021-01-01 11:00:00'
LIMIT 1;
Note that this is returning customer_id from the orders table. That is how it is NULL if there is no match.
The above does assume that you have at least one customer, which seems like a reasonable assumption.
An alternative is UNION ALL:
WITH c as (
SELECT customer_id
FROM customer c JOIN
order o
USING (customer_id)
WHERE o.time >= '2021-01-01 10:00:00' AND
o.time < '2021-01-01 11:00:00'
LIMIT 1
)
SELECT c.customer_id
FROM c
UNION ALL
SELECT NULL
WHERE NOT EXISTS (SELECT 1 FROM c);
And a third alternative might be even simpler, a subquery:
select (select o.customer_id
from order o
where o.time >= '2021-01-01 10:00:00' AND
o.time < '2021-01-01 11:00:00'
limit 1
) as customer_id;
If the subquery returns no rows, then the result is NULL.
The simplest solution would be to check "left joined" table row
select
case when c.customer_id is null then null else o.customer_id end
from order as o
left join customer_cte as c using (customer_id)
So you have to tables Customers and Orders, and there is a one-to-many relation between Customers and Orders: Every Customer has zero or more Orders, every Order belongs to exactly one Customer, namely the Customer that the foreign key CustomerId refers to.
Every Customer has a property Time. (Are you sure Time is a Customer property, not a property of an Order?). You have two DateTime values: timeStart and timeEnd, and from every Customer that has a value for property time between timeStart and timeEnd, you want one of his Orders, doesn't matter which one, or null if this Customer doesn't have any Orders.
In baby steps:
DateTime timeStart = ... // = '2021-01-01 10:00:00'
DateTime timeEnd = ... // = '2021-01-01 11:00:00'
IQueryable<Order> allOrders = ...
IQueryable<Customer> allCUstomers = ...
IQueryable<Customer> customersWithinTimeRanges = allCustomers
.Where(customer => timeStart <= customer.Time and customer.Time < timeEnd);
In words: from all Customers, keep only those Customers that have a value of property Time that is between timeStart and timeEnd (using propery <= and <)
Now from every Customer within time range, you want one of his Orders, or null if the Customer doesn't have any Order.
Whenever you have a one-to-many relation, like Schools with their zero or more Students, Cities with their zero or more inhabitants, or in your case: Customers with their zero or more Orders, and from every "one" you want zero or more of his "many" items, consider to use one of the overloads of Queryable.GroupJoin
I almost always need the overload with a parameter resultSelector
var customersWithAnOrder = customersWithinTimeRanges.GroupJoin(
allOrders,
customer => customer.Id, // from every Customer take the primary key
order => order.CustomerId, // from every Order take the foreign key
// parameter resultSelector: from every Customer with his zero or more Orders
// make one new object:
(customer, ordersOfThisCustomer) => new
{
// select the Customer properties that you plan to use:
Id = customer.Id,
Name = customer.Name,
...
Order = ordersOfThisCustomer.Select(order => new
{
// select the Order properties that you plan to use
Id = order.Id,
Date = order.Date,
...
// not needed, you already got the value in property Id:
// CustomerId = order.CustomerId
})
// you don't need all Orders of this Customer, you only want one,
// don't care which one or null if this Customer doesn't have any Orders
.FirstOrDefault(),
});
Sometimes the database doesn't allow you to do FirstOrDefault if you haven't sorted the sequence. In that case, sort before FirstOrDefault:
Order = ordersOfOfThisCustomer.Select(order => new {...})
.OrderBy(order => order.Date) // or whatever you want to sort on
.FirstOrDefault(),
