How to Include Default Values in an IN Query for Missing Date Range Data?

Get default value for IN query value

Suppose you have a query that uses an IN query to retrieve data for users who were online within a specific date range. However, you want to include a default value in the IN condition for user IDs that do not match the date range condition.

Question

The simplified query below uses a left join to match online usage to user IDs. However, it only returns records that match the date range criteria.

<code class="language-sql">SELECT users.Name, users.ID, SUM(users.Minutes) AS MinutesOnline
FROM UserTable
LEFT JOIN OnlineUseage ON OnlineUseage.ID = UserTable.ID
WHERE OnlineUseage.Date >= '2016-01-01 00:00:00' AND OnlineUseage.Date < '2016-01-31 00:00:00';</code>

Solution

To include a default value, place the date range condition in the FROM clause:

<code class="language-sql">SELECT
    users.Name,
    users.ID,
    IFNULL(SUM(users.Minutes), 0) AS MinutesOnline
FROM
    users
LEFT JOIN OnlineUseage ON
        OnlineUseage.ID = users.ID and
        OnlineUseage.Date >= '2016-01-01 00:00:00' AND
        OnlineUseage.Date < '2016-01-31 00:00:00';</code>

This ensures that all user IDs are included in the retrieval IN condition, regardless of whether they match the date range condition. IFNULL is used to provide a default value of 0 for users without online usage data.

The above is the detailed content of How to Include Default Values in an IN Query for Missing Date Range Data?. For more information, please follow other related articles on the PHP Chinese website!