asked on

recursive SQL

Hi

I have two tables:
Question Response
---------- -----------
curr_id curr_id
prev_id score

The question table holds a list of questions each with its own unique ID. The questions can change over time and when a change happens a new question is loaded into the question table with a new curr_id, The curr_id of the old question that the new one changed from is not removed from the table and to signify that the new question stemed from the old the old question Curr_id is placed into the new questions prev_id field. The response table holds numeric responses to the questions.

Some possible values in these tables may be:

curr_id prev_id Qstn_id Score
1 -        1 1
2 1 2 1
3 -        3 1
4 -        4 1
5 3        5 1
6 -        6 1
7 5        7 1

I want to find some SQL that will give a sum of all the scores for each question (excluding the old ones) that inclues the values of all previous versions of the question. So for example using the values above question 7 should receive a sumed up score of 3 (1 from id=7 1 from id=5 and 1 from id=3). I need to do this with straight SQL that can be used on multiple DBs. I've been struggling with this for a few days now with out any success. Does any one know how to do this or wether its possible.

Thanks
David

Zvika

Do you have any maximum number of possible "revisions" for a question?
If not, Standard SQL (ANSI) can not give you what you need. Maybe T-QSL or something else (depends on your database type) can do such actions.
if you do have a maximum number, maybe I can write something for you.

Zvika

RaisinJ

What type of DB are your using, SQLServer 7/2000?

NHBFighter

ASKER

HI
There is no max number for question revesions, and i need it in standard SQL because I will be using that sql to pull the data from a star schema and into an Esspase cube. I can do this by using a standard programing language like Java to create a flat file containing the data but I wanted to try and do it using SQL. I'm not sure it can be done either, but I thought there might be something I'm missing.
The DB I'm using is acctually oracle 9i.

Thanks
Dave

RaisinJ

Is it an option for you to write a stored procedure in Oracle (PL/SQL)?

NHBFighter

ASKER

I'm not sure, it might be. I'll have to look into it but it may take a few days.
Thanks

Zvika

Dave
>> I'm not sure it can be done either, but I thought there might be something I'm missing
As i wrote before, if you don't have an upper limit for revisions, I am sure it cannot be done in standard SQL.
So, the answer for your question is "Can't be done".
As for workarounds - your idea of using programming language sounds quite reasonable for the case.
RaisinJ's idea is also possible - than again it won't be Standard SQL, but will use Oracle specific functions.
Another option is to modify your DB so it will include a straight connection from a revision to the question ID. An idea for such a design that will not suffer from duplication of data is to have one table of QuestionIDs that will have a fixed number for each ID, and another table that will have the reviosion, something like:
QuestionID
Revision
Score
so instead of having a fixed number for each revision the PK of a revision will be the questionID + revision number. I think it's more flexible data-structure, but am not sure if changing your database schema is acceptable for you.

Good luck
Zvika

NHBFighter

ASKER

I guess the correct answer is that it can't be done using standard SQL. We actually changed the busniess rule to state that if a question is revised an update is made to the original record instead of a new row being inserted. I never lilke the way the original DB was set up anyway :)

Thanks
Dave

ASKER CERTIFIED SOLUTION

Zvika

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NHBFighter

ASKER

Works for me

Zvika

Thanks Fighter

Better late than never ;)