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;