MySQL: Conditional statement within query

Question

I'll try to be straight to the point of what I am trying to accomplish here...

I have a query that works but I have recently discovered an issue with it. Now, I could simply fix this with PHP but I know it can be done somehow in MySQL... I just don't know how. So this is what is going on:

I have a USERS table and a SERVICES table. A user can have multiple SERVICES (each service being completely independent from one another) and every service has a field called "status". Status defines the current state the service is in: active / inactive

The query is called when you go to the Manage Users page, where every user is split into the category it belongs. For this example, it will be: Active Users | Deactivated Users | Other Users (users without any service plan at all).

So my deactivated users query comes down to this:

 SELECT DISTINCT u.* FROM users u LEFT JOIN services s ON s.assignedto=u.id WHERE s.status!=1

The statement works great, however, if the customer has 2 services plans where one is activated and one is deactivated, then he will appear in the deactivated list as well as the activated list. The statement needs a condition where it will exclude the user from the query if they ALSO have an activated service plan. Right now, I have a few users that are falling into the deactivated users that should not be there.

Anyway, thank you in advance!

score 2 · Accepted Answer · answered Nov 25 '13 at 18:41

2

You could rewrite your query with not exits() as @GarethD suggested.

Another approach to have in mind if it doesn't scale well could be a group by clause, e.g.:

SELECT u.*
FROM users u
LEFT JOIN services s ON s.assignedto=u.id
GROUP BY u.id
HAVING max(s.status) <> 1

The best option, though, would be an extra field. If you add a status field to users, you could index and query that directly. You could maintain it using a trigger but, speaking personally, this is a rare case where I've found that maintaining it in the app is a better approach. (Basically, you never know when you need to quickly mark a user as inactive without messing around with other DB tables.)

answered Nov 25 '13 at 18:41

Denis de Bernardy

75,850
13
131
154

Awesome. Yeah, I thought about that approach originally but relying on a field to determine the inactivity of a user (I feel) is setting myself up to fail at some point. For any case, when a service becomes deactivated (failed payments, manual deactivation, etc..), I would have to tick that field to be deactivated. Though, this SQL query solves it all :). Thank you! – user0000001 Nov 25 '13 at 21:31
1

@Chris: long-term, it's the better option for performance reasons. If you want it fail-proof, manage it using a trigger, and add a separate field in case you need to manually override it. You'll have no chance of error by proceeding that way, and an index as a bonus. – Denis de Bernardy Nov 25 '13 at 21:49
@Denis I could see it getting hefty on performance eventually. If I want to use your statement to count the number of rows, what would I do? I tried doing `count(DISTINCT u.id)` but it keeps returning 1. – user0000001 Nov 26 '13 at 00:02
@Denis Sorry for the delay, was out of town. I had already tried count(*) but still receiving 1 in 9 different rows. The 9 rows however reflects exactly how many users are deactivated. I'm guessing this is being caused by the `GROUP BY`. – user0000001 Dec 04 '13 at 21:55
You could use a subquery (easiest). A window function likely gets the same result without the group by: `having (s.status) over (partition by u.id) <> 1` -- you can then count(*) accordingly. – Denis de Bernardy Dec 05 '13 at 19:10

score 0 · Answer 2 · edited May 23 '17 at 12:05

You can exclude users with active services using NOT EXISTS:

SELECT  u.* 
FROM    users u 
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    Services s
            WHERE   s.assignedto=u.id 
            AND     s.status = 1
        );

To get the counts you are after I think you need something like this:

SELECT  COUNT(CASE WHEN s.ActiveServices > 0 THEN 1 END) AS ActiveUsers,
        COUNT(CASE WHEN s.ActiveServices = 0 THEN 1 END) AS DeactivatedUsers,
        COUNT(CASE WHEN s.assignedto IS NULL THEN 1 END) AS OtherUsers
FROM    users u 
        LEFT JOIN
        (   SELECT  s.assignedto,
                    COUNT(CASE WHEN s.status = 1 THEN 1 END) AS ActiveServices
            FROM    Services s
            GROUP BY s.assignedto
        ) s
            ON s.assignedto = u.ID

I have just remembered that NOT EXISTS is not as efficient as LEFT JOIN/IS NULL in MySQL, Denis has also pointed out that NOT EXISTS does not scale well. The LEFT JOIN Approach would be:

SELECT  u.*
FROM    Users u
        LEFT JOIN Services s
            ON s.assignedto = u.id
            AND s.status = 1
WHERE   s.assignedto IS NULL;

MySQL: Conditional statement within query

2 Answers2