I'm afraid I'm not quite clear on what you're trying to do; do you want one
result per row, with one NULL column and one non-NULL column, or do you want
one row per distinct 'machine', with both a start and a stop date?
In the first case, this can be implemented with a simple IF():
IF(field_function='TT',field_date,NULL)
which will make the field NULL if field_function is not set to the correct
value.
If you want two separate fields (both date and time), then use two IFs with
the same test.
If you want one row for each machine with both start and end dates, there
are several ways to do it. Which you choose depends largely on your
integrity contraints; are you sure that you've got exactly one TT and one BT
for each machine, or do you have 'pairs', or do you want every comination?
The simplest hack is just to use the above, but wrap every query in a max()
function, and group by the field_machine:
select
max(field_machine) as fieldMachine,
max(if(field_function='TT',field_date,NULL)) as startDate,
max(if(field_function='TT',field_time,NULL)) as startTime,
max(if(field_function='BT',field_date,NULL)) as stopDate,
max(if(field_function='BT',field_time,NULL)) as stopTime
from theTable
group by fieldMachine
I consider this ugly, but it's simple to do.
If you're really trying to relate each machine to its start and end times,
then I would write the query that way: get a table of distinct machines (if
it exists already, great; if not,
create temporary table theMachines select distinct field_machine from
theTable;
) and then join that table against your start times and against your end
times:
select theMachines.field_machine,
theStart.field_date, theStart.field_time,
theEnd.field_date, theStart.field_time
from theMachines, theTable as theStart, theTable as theEnd
where theMachines.field_machine = theStart.field_machine
and theStart.field_function = 'TT'
and theMachines.field_machine = theEnd.field_machine
and theEnd.field_function = 'BT'
(Just banging this out quickly, but you get the idea.)
-rob
On 7/6/02 at 12:59 pm, Fabrizio Tivano [EMAIL PROTECTED] wrote:
Hello dear all,
i have a table like this:
field_machine, field_date, field_time, field_function.
field_function can be =TT or +BT
well i need to perform a select query, witch display on one line
field_machine,
(field_date, field_time where field_function =TT) AS start,
(field_date, fiel_time where field_function =BT) AS stop from table;
is possible to do that?
and if yes, how?
thanks in advance and Regards,
fabrizio
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
