I'm trying to query a table that contains a column of status'. 1 being active, 2 being inactive. I want to return a list of customer_id's that are inactive. It gets complicated because some customers could have inactive AND active jobs going at the same time. So what I want is a list of only inactive clients with no other projects active. It's late and I'm having a hard time explaining, but hopefully this and the attached table will help explain what I'm trying to do.
Thanks!
Query Project Status
Moderator: General Moderators
Query Project Status
- Attachments
-
- Screen shot 2011-02-03 at 11.16.05 PM.png (61.23 KiB) Viewed 1708 times
Re: Query Project Status
Code: Select all
select
`customer_id`,
count(if(`status`=1, true, null)) as `active_count`
from
`projects`
group by
`customer_id`
having
`active_count`=0There are 10 types of people in this world, those who understand binary and those who don't
Re: Query Project Status
Yep, exactly.VladSun wrote:According to data shown, I think it's "1 being active, 0 being inactive" ...Code: Select all
select `customer_id`, count(if(`status`=1, true, null)) as `active_count` from `projects` group by `customer_id` having `active_count`=0
I tried that query but it comes back with the following:
[text]customer_id active_count
5 11[/text]
Re: Query Project Status
There is no way to produce such result with my query.jamgood96 wrote: I tried that query but it comes back with the following:
[text]customer_id active_count
5 11[/text]
Because of the
Code: Select all
HAVING
`active_count`=0PS: Did you clicked the "Expand" code-block button? I.e. did you write the whole query?
There are 10 types of people in this world, those who understand binary and those who don't
Re: Query Project Status
VladSun wrote:There is no way to produce such result with my query.jamgood96 wrote: I tried that query but it comes back with the following:
[text]customer_id active_count
5 11[/text]
Because of thethere is no chance to have result rows with non-zero active_countCode: Select all
HAVING `active_count`=0
PS: Did you clicked the "Expand" code-block button? I.e. did you write the whole query?
Bingo. I only got the first few lines. Sorry, bit of a newb here. Thanks so much for your help!