- Feb 13, 2003
- 26,256
- 406
- 126
I don't think this is a difficult query but I just can't seem to get it to work right.
I have a table with 4 columns:
- ID (primary key, auto increment int)
- Task (string, either "TaskOne" or "TaskTwo")
- StartedOn (datetime)
- CompletedOn (datetime)
Now most IDs have a record for "TaskOne" and a record for "TaskTwo", but some have only a single record for "TaskOne".
The query I need to modify selects from the table, and does a join to that same table, to get a result set that looks like:
ID----TaskOneStart----TaskOneEnd---TaskTwoStart----TaskTwoEnd
__________________________________________________________
1------datetime--------datetime-------datetime---------datetime--
2------datetime--------datetime-------datetime---------datetime--
And so on. The problem is, if there's no "TaskTwo" record for a given ID, it is left out of the result set. I'd like to have it included, and for the TaskTwoStart/TaskTwoEnd columns just put NULL in there or something.
The current query is as follows:
Any suggestions? Thanks much.
I have a table with 4 columns:
- ID (primary key, auto increment int)
- Task (string, either "TaskOne" or "TaskTwo")
- StartedOn (datetime)
- CompletedOn (datetime)
Now most IDs have a record for "TaskOne" and a record for "TaskTwo", but some have only a single record for "TaskOne".
The query I need to modify selects from the table, and does a join to that same table, to get a result set that looks like:
ID----TaskOneStart----TaskOneEnd---TaskTwoStart----TaskTwoEnd
__________________________________________________________
1------datetime--------datetime-------datetime---------datetime--
2------datetime--------datetime-------datetime---------datetime--
And so on. The problem is, if there's no "TaskTwo" record for a given ID, it is left out of the result set. I'd like to have it included, and for the TaskTwoStart/TaskTwoEnd columns just put NULL in there or something.
The current query is as follows:
Code:
SELECT mql1.ID, mql1.StartedOn AS TaskOneStart,
mql1.CompletedOn AS TaskOneFinished, mql2.StartedOn AS TaskTwoStart,
mql2.CompletedOn AS TaskTwoFinished
FROM myTable mql1
INNER JOIN myTable mql2 ON mql1.ID = mql2.ID
WHERE (mql1.Task = 'TaskOne') AND (mql2.Task = 'TaskTwo')
Any suggestions? Thanks much.