[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

recursive SQL

Posted on 2004-10-09
12
Medium Priority
?
248 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
10 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
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 1600 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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