Solved

recursive SQL

Posted on 2004-10-09
12
237 Views
Last Modified: 2009-12-16
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
0
Comment
Question by:NHBFighter
  • 4
  • 4
  • 2
12 Comments
 
LVL 4

Expert Comment

by:Zvika
ID: 12269566
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
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12271534
What type of DB are your using, SQLServer 7/2000?
0
 
LVL 4

Author Comment

by:NHBFighter
ID: 12272788
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
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12272808
Is it an option for you to write a stored procedure in Oracle (PL/SQL)?
0
 
LVL 4

Author Comment

by:NHBFighter
ID: 12272941
I'm not sure, it might be. I'll have to look into it but it may take a few days.
Thanks
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:Zvika
ID: 12274804
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
0
 
LVL 4

Author Comment

by:NHBFighter
ID: 12718361
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
0
 
LVL 4

Accepted Solution

by:
Zvika earned 400 total points
ID: 12723369
I don't see why the question should be closed...
NHBFighter said: "I guess the correct answer is that it can't be done using standard SQL."
this is exactly the answer I gave him in the first comment on this page
(>> Do you have any maximum number of possible "revisions" for a question? If not, Standard SQL (ANSI) can not give you what you need.)
and again on the last comment on this page
(>> 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".)

I think I gave him a full answer, no?
0
 
LVL 4

Author Comment

by:NHBFighter
ID: 12723379
Works for me
0
 
LVL 4

Expert Comment

by:Zvika
ID: 12723412
Thanks Fighter

Better late than never ;)
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now