Page 1 of 1

Query Project Status

Posted: Fri Feb 04, 2011 1:20 am
by jamgood96
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!

Re: Query Project Status

Posted: Fri Feb 04, 2011 7:09 am
by VladSun

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
According to data shown, I think it's "1 being active, 0 being inactive" ...

Re: Query Project Status

Posted: Fri Feb 04, 2011 10:38 am
by jamgood96
VladSun wrote:

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
According to data shown, I think it's "1 being active, 0 being inactive" ...
Yep, exactly.

I tried that query but it comes back with the following:
[text]customer_id active_count
5 11[/text]

Re: Query Project Status

Posted: Fri Feb 04, 2011 10:43 am
by VladSun
jamgood96 wrote: I tried that query but it comes back with the following:
[text]customer_id active_count
5 11[/text]
There is no way to produce such result with my query.
Because of the

Code: Select all

HAVING 
        `active_count`=0
there is no chance to have result rows with non-zero active_count

PS: Did you clicked the "Expand" code-block button? I.e. did you write the whole query?

Re: Query Project Status

Posted: Fri Feb 04, 2011 11:06 am
by jamgood96
VladSun wrote:
jamgood96 wrote: I tried that query but it comes back with the following:
[text]customer_id active_count
5 11[/text]
There is no way to produce such result with my query.
Because of the

Code: Select all

HAVING 
        `active_count`=0
there is no chance to have result rows with non-zero active_count

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!