Hello all,
First let me say thank you very much for a very useful mailing list.
I have a somewhat complex SQL query question. Given the table:
CREATE TABLE times(name CHAR(32) not null,
time1 CHAR(10),
time2 CHAR(10),
time3 CHAR(10));
The 3 time fields will hold an integer (or an 'X' if no time is
supplied).
I would like to do a single query that returns four columns:
The name. (all the names should be returned)
A field with 0 if all time1=='X' for that name (>0 otherwise).
A field with 0 if all time2=='X' for that name (>0 otherwise).
A field with 0 if all time3=='X' for that name (>0 otherwise).
For example, if the table held:
name | time1 | time2 | time3
Hoyhoy | 300 | X | X
Hoyhoy | X | 33 | X
Hoyhoy | 300 | X | 4
Fred | X | 44 | X
Fred | X | 44 | X
Tony | 100 | 1 | X
The query should return exactly 3 records:
Hoyhoy | 2 | 1 | 1
Fred | 0 | 2 | 0
Tony | 1 | 1 | 0
Alternatively we could use:
Hoyhoy | T | T | T
Fred | F | T | F
Tony | T | T | F
Or 0/1 since the exact count is unimportant.
Thank you for any help you can provide.
