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
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
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!