Query Project Status

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jamgood96
Forum Newbie
Posts: 12
Joined: Fri Jan 21, 2011 12:19 am

Query Project Status

Post 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!
Attachments
Screen shot 2011-02-03 at 11.16.05 PM.png
Screen shot 2011-02-03 at 11.16.05 PM.png (61.23 KiB) Viewed 1709 times
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Query Project Status

Post 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" ...
There are 10 types of people in this world, those who understand binary and those who don't
jamgood96
Forum Newbie
Posts: 12
Joined: Fri Jan 21, 2011 12:19 am

Re: Query Project Status

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Query Project Status

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
jamgood96
Forum Newbie
Posts: 12
Joined: Fri Jan 21, 2011 12:19 am

Re: Query Project Status

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