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!

Denis de Bernardy · Accepted Answer · 2013-11-25 18:41:02Z

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, 2013 at 18:41

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

5 Comments

user0000001 Over a year ago

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!

Denis de Bernardy Over a year ago

@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.

user0000001 Over a year ago

@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 Over a year ago

@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.

Denis de Bernardy Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:05:05Z

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;

Collectives™ on Stack Overflow

MySQL: Conditional statement within query

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related