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 meta.stackoverflow.com/questions/333952/… — Strawberry
– Strawberry, Commented Nov 23, 2017 at 8:29
Does this answer your question? Left Outer Join doesn't return all rows from my left table? — philipxy
– philipxy, Commented Apr 2, 2020 at 18:57

Indent · Accepted Answer · 2017-11-23 07:27:54Z

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, 2017 at 7:27

Indent

4,9671 gold badge22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

steve Over a year ago

Awesome! Can't believe I didn't think about moving the condition into the JOIN part. Thanks. Will mark as correct when allowed.

Indent Over a year ago

conditions on OnlineUseage.Date are applied before the join if they are on from clause

Indent Over a year ago

if you want select users.Name you need add it on group by (it's better). With another database it's mandatory.

Sandeepan Nath Over a year ago

@Indent My question has been marked duplicate against this one - stackoverflow.com/questions/50849705/…. Could you please help?

Barmar · Accepted Answer · 2017-11-23 07:28:16Z

0

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,.........)

answered Nov 23, 2017 at 7:28

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

Return default result for IN value regardless

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related