Return default result for IN value regardless

Question

I have a query that gets all user that have been online between a certain date range. This is done via an IN query due to how I want to display the data. However, I would like to return a default value if no records were found for an ID that was parsed into the IN condition.

Simplified Query:

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-12-31 23:59:59'
AND UserTable.ID IN(332,554,5764,11,556,.........)
GROUP BY users.ID
ORDER BY FIELD(UserTable.ID, 332,554,5764,11,556,.........)

Now the above query will only pull in those row that meet the condition, as expected. I would also like the query to pull in a default value for the ID's within the IN condition that don't meet the condition.

Using IFNULL in this instance will not work as the record is never returned

SELECT users.Name, users.ID, IFNULL(SUM(users.Minutes), 0) 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-12-31 23:59:59'
AND UserTable.ID IN(332,554,5764,11,556,.........)
GROUP BY users.ID
ORDER BY FIELD(UserTable.ID, 332,554,5764,11,556,.........)

FYI - i'm parsing this query into a custom PDO function. I'm not using deprecated mysql functions

There is no users table here. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Nov 23 '17 at 08:29
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Apr 02 '20 at 18:57

score 2 · Accepted Answer · answered Nov 23 '17 at 07:27

2

You have a condition on OnlineUseage the left join become like a inner join.

move your condition to the from clause will be better :

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-12-31 23:59:59'
WHERE
    users.ID IN (332,554,5764,11,556,.........)
GROUP BY
    users.ID,users.Name
ORDER BY
    users.ID

answered Nov 23 '17 at 07:27

Indent

4,675
1
19
35

Awesome! Can't believe I didn't think about moving the condition into the JOIN part. Thanks. Will mark as correct when allowed. – steve Nov 23 '17 at 07:33
conditions on `OnlineUseage.Date` are applied before the join if they are on from clause – Indent Nov 23 '17 at 07:35
if you want select users.Name you need add it on group by (it's better). With another database it's mandatory. – Indent Nov 23 '17 at 07:38
@Indent My question has been marked duplicate against this one - https://stackoverflow.com/questions/50849705/unable-to-get-desired-output-using-mysql-joins-table-schema-and-test-data-prov/. Could you please help? – Sandeepan Nath Jun 14 '18 at 06:18

score 0 · Answer 2 · answered Nov 23 '17 at 07:28

When you use LEFT JOIN you have to put the conditions on the second table into the ON clause. Putting them in the WHERE clause filters out all the rows that don't have matches, because those columns will be NULL and the conditions will never succeed.

SELECT users.Name, users.ID, IFNULL(SUM(OnlineUseage.Minutes), 0) AS MinutesOnline
FROM UserTable
LEFT JOIN OnlineUseage ON OnlineUseage.ID = UserTable.ID
    AND OnlineUseage.Date >= '2016-01-01 00:00:00' AND OnlineUseage.Date <= '2016-12-31 23:59:59'
WHERE UserTable.ID IN(332,554,5764,11,556,.........)
GROUP BY UserTable.ID
ORDER BY FIELD(UserTable.ID, 332,554,5764,11,556,.........)

Return default result for IN value regardless

2 Answers2

Linked

Related