mysql using user-defined variable in where clause

Question

sql query:

select
    u.username,
        @total_subscribers:=(
            select count(s.id)
                from subscribers as s
                where s.suid = u.uid
        ) as total_subscribers
from users as u
where @total_subscribers > 0

if i remove where @total_subscribers > 0 query will show all users and their total subscribers

but i want to show only those users who have at least 1 subscriber... after i add the where clause and use the defined variable i get an empty result set.

juergen d · Accepted Answer · 2012-06-07 15:39:34Z

3

You can do it with group by and having:

select
   u.username,
   count(s.id) as total_subscribers
from users as u
inner join subscribers as s on s.suid = u.uid
group by u.id
having count(s.id) > 0

edited Jun 7, 2012 at 15:39

answered Jun 7, 2012 at 15:32

juergen d

205k40 gold badges303 silver badges376 bronze badges

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

Comments

Quassnoi · Accepted Answer · 2012-06-07 15:40:51Z

1

MySQL does not guarantee the order of evaluation of the session variables in this case.

From the docs:

The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server

Use this:

SELECT  u.*, COUNT(*)
FROM    users u
JOIN    subscribers s
ON      s.suid = u.uid
GROUP BY
        u.uid

The JOIN makes sure you have at least one subscriber.

edited Jun 7, 2012 at 15:40

answered Jun 7, 2012 at 15:35

Quassnoi

427k94 gold badges627 silver badges622 bronze badges

Collectives™ on Stack Overflow

mysql using user-defined variable in where clause

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related