Help with a oracle select query

hello everybody!

I'm in a trouble.... can anybody help me... ?

well I have one log DATABASE table like this:

tablename: logs



I need to extract the time between questions and the respective answers
Because we have others db fields (here I wrote just a simple example) I' ll need to use the date with a parameter
I'll need to take the question and the next 'answer' using the date , I think! and calculate the time between the registers...

I wrote a SELECT like this:

SELECT a.office, a.user, a.event, b.event, a.date, b.date
FROM logs a, logs b
WHERE a.office = b.office AND a.user = b.user AND ??????

well... here is my problem
I cant link the respective rows

+-------+-----+------------+----------------------+
| office | user | event | date |
+-------+-----+------------+----------------------+
| seller | mike | question | 12/12/2007 10:12 |
| seller | mike | answer | 12/12/2007 10:14 |
| seller | mike | question | 12/12/2007 10:25 |
| seller | mike | answer | 12/12/2007 10:30 |
| seller | tom | question | 12/12/2007 10:33 |
| seller | tom | answer | 12/12/2007 10:34 |
| seller | mike | question | 12/12/2007 10:45 |
| seller | mike | answer | 12/12/2007 10:50 |
+-------+-----+------------+----------------------+


the query replies this:

| seller | mike | question | 12/12/2007 10:12 | | seller | mike | answer | 12/12/2007 10:14 |
| seller | mike | question | 12/12/2007 10:12 | | seller | mike | answer | 12/12/2007 10:30 |
| seller | mike | question | 12/12/2007 10:12 | | seller | tom | answer | 12/12/2007 10:34 |
| seller | mike | question | 12/12/2007 10:12 | | seller | mike | answer | 12/12/2007 10:50 |
and
| seller | mike | question | 12/12/2007 10:25 || seller | mike | answer | 12/12/2007 10:14 |
| seller | mike | question | 12/12/2007 10:25 || seller | mike | answer | 12/12/2007 10:30 |
| seller | mike | question | 12/12/2007 10:25 || seller | tom | answer | 12/12/2007 10:34 |
| seller | mike | question | 12/12/2007 10:25 || seller | mike | answer | 12/12/2007 10:50 |
and...

but I need just:
| seller | mike | question | 12/12/2007 10:12 | | seller | mike | answer | 12/12/2007 10:14 |
| seller | mike | question | 12/12/2007 10:25 || seller | mike | answer | 12/12/2007 10:30 |
...

waiting and thanks ....

Well first you need to fix your table. You need a primary key and a foriegn key to get this working properly.

As for figuring out the difference in time, just do question_date - answer_date. It will give you a number that represents the difference in days

ok!
step-by-step I'll need first the reply:


how can I do this reply without moddify the table, just using SELECTs, JOINs and UNIONs and .... ???

thnx

You going to have to modify your table to fix this issue. Currently, you have no way of mapping your answer row to your question row, thats why you need to add the PK and FK.

how can I do this?





----
I add to the query : answer_day > question_day

and the reply was:



if could I ignore the others repeated questions rows the reply would like this ...






ok! more... I can use the date like a primary key because is unique.... and the fk ... ?


can I discard repeated answers using the date question?

Well, can there be multiple answers to each question? if so, i would create a second table (lets call it Answers) it can be formatted just like you current table. you will then need to add another numeric column (lets call it answers2questions) that will be used to link the table to the questions table.

now on the questions table, you will also need to add a new numeric column to use as your primary key (say, answers_id). You will then have to give each row its own unique number. The number assigned will then have to go into the answers2questions column.

Overall, you may want to read up on your DB design, and it would be really good to read up on 3rd level of normalization.