SEO

#1 (**permalink**) 03-20-2008, 12:47 PM

Hi !

I need some help to build a query.
I have a simple database with two tables.

the first table : "reports" has two fields "id" and "name"
the second table: "actions" has 3 fields "id" , "report_id" and
"state"

I want to list all reports with report's state. the state depends of
actions state

if a report has no action, the state is closed.
if all actions state of a report are closed , the state is closed.
if one action's state is other than closed, the state is running.

result :
report_id | report_name | report_state

I think it is possible to make only one query but I can't make it...

Somebody can help me?

Seb

PS : Sorry for my english...

#2 (**permalink**) 03-20-2008, 12:47 PM

On Mon, 17 Mar 2008 10:43:32 -0700, sebastien.ramage@gmail.com wrote:

> Hi !
>
> I need some help to build a query.
> I have a simple database with two tables.
>
> the first table : "reports" has two fields "id" and "name" the second
> table: "actions" has 3 fields "id" , "report_id" and "state"
>
> I want to list all reports with report's state. the state depends of
> actions state
>
> if a report has no action, the state is closed. if all actions state of
> a report are closed , the state is closed. if one action's state is
> other than closed, the state is running.
>
> result :
> report_id | report_name | report_state
>
> I think it is possible to make only one query but I can't make it...
>
> Somebody can help me?
>
> Seb
>
> PS : Sorry for my english...

How about:

SELECT
report_id,
report_name,
IF(
SUM(
IF(
action_state == 'closed',
0,
1
)
) > 0,
'running',
'closed'
)
FROM reports
LEFT JOIN actions
ON reports.report_d == actions.report_id
GROUP BY report_id, report_name;
--
Remove caps to reply

#3 (**permalink**) 03-20-2008, 12:47 PM

On 17 mar, 19:09, PleegWat <pleegwat.REM...@CAPS.telfort.nl.INVALID>
wrote:
> On Mon, 17 Mar 2008 10:43:32 -0700, sebastien.ram...@gmail.com wrote:
> > Hi !
>
> > I need some help to build a query.
> > I have a simple database with two tables.
>
> > the first table : "reports" has two fields "id" and "name" the second
> > table: "actions" has 3 fields "id" , "report_id" and "state"
>
> > I want to list all reports with report's state. the state depends of
> > actions state
>
> > if a report has no action, the state is closed. if all actions state of
> > a report are closed , the state is closed. if one action's state is
> > other than closed, the state is running.
>
> > result :
> > report_id | report_name | report_state
>
> > I think it is possible to make only one query but I can't make it...
>
> > Somebody can help me?
>
> > Seb
>
> > PS : Sorry for my english...
>
> How about:
>
> SELECT
> report_id,
> report_name,
> IF(
> SUM(
> IF(
> action_state == 'closed',
> 0,
> 1
> )
> ) > 0,
> 'running',
> 'closed'
> )
> FROM reports
> LEFT JOIN actions
> ON reports.report_d == actions.report_id
> GROUP BY report_id, report_name;
> --
> Remove caps to reply

oh thank you !

what is the difference with :

SELECT report_id,report_name,
IF(
SUM(
IF(
action_state == 'closed',
0,
1
)
) > 0,
'running',
'closed'
)
FROM reports,actions
WHERE reports.report_d == actions.report_id
GROUP BY report_id, report_name;

#4 (**permalink**) 03-20-2008, 12:47 PM

On Mon, 17 Mar 2008 11:19:34 -0700, sebastien.ramage@gmail.com wrote:

> On 17 mar, 19:09, PleegWat <pleegwat.REM...@CAPS.telfort.nl.INVALID>
> wrote:
>> On Mon, 17 Mar 2008 10:43:32 -0700, sebastien.ram...@gmail.com wrote:
>> > Hi !
>>
>> > I need some help to build a query.
>> > I have a simple database with two tables.
>>
>> > the first table : "reports" has two fields "id" and "name" the second
>> > table: "actions" has 3 fields "id" , "report_id" and "state"
>>
>> > I want to list all reports with report's state. the state depends of
>> > actions state
>>
>> > if a report has no action, the state is closed. if all actions state
>> > of a report are closed , the state is closed. if one action's state
>> > is other than closed, the state is running.
>>
>> > result :
>> > report_id | report_name | report_state
>>
>> > I think it is possible to make only one query but I can't make it...
>>
>> > Somebody can help me?
>>
>> > Seb
>>
>> > PS : Sorry for my english...
>>
>> How about:
>>
>> SELECT
>> report_id,
>> report_name,
>> IF(
>> SUM(
>> IF(
>> action_state == 'closed',
>> 0,
>> 1
>> )
>> ) > 0,
>> 'running',
>> 'closed'
>> )
>> FROM reports
>> LEFT JOIN actions
>> ON reports.report_d == actions.report_id
>> GROUP BY report_id, report_name;
>> --
>> Remove caps to reply
>
> oh thank you !
>
> what is the difference with :
>
> SELECT report_id,report_name,
> IF(
> SUM(
> IF(
> action_state == 'closed',
> 0,
> 1
> )
> ) > 0,
> 'running',
> 'closed'
> )
> FROM reports,actions
> WHERE reports.report_d == actions.report_id GROUP BY report_id,
> report_name;

The latter doesn't give results for a report without actions.

This isn't homework is it?

--
Remove caps to reply

#5 (**permalink**) 03-20-2008, 12:47 PM

PleegWat wrote:
> On Mon, 17 Mar 2008 11:19:34 -0700, sebastien.ramage@gmail.com wrote:
>
>
> This isn't homework is it?
>

too late to be asking that question isn't it?

#6 (**permalink**) 03-20-2008, 12:47 PM

On 17 mar, 23:40, Michael Austin <maus...@firstdbasource.com> wrote:
> PleegWat wrote:
> > On Mon, 17 Mar 2008 11:19:34 -0700, sebastien.ram...@gmail.com wrote:
>
> > This isn't homework is it?
>
> too late to be asking that question isn't it?

no it's not homework, I work for a small company and I'm building my
own database driven app , but I think I need to learn more about
Mysql

thank you for your help

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Need help for building a query