Not possible Stored Procedure? 1

[mnasuto]

Hi,

I have 2 tables with fields:
tbl_Question

Questin_ID,
Question,
Depends

tbl_Answer

Company_ID
Question_ID
Answer

I do not have Company_ID field in tbl_Question.

Data Example:Company-id,Question_id,Question,Answer,Depends
10, 1, Q1, A1
10, 2, Q2, A2, 1
10, 3, Q3, A3, 2
10, 4, Q4, A4, 2
10, 5, Q5, A5, 4
10, 6, Q6, A6,

This data shows Questions with dapendency, exp. Question2 -Q2 is under Question Q1 and Questions Q3, Q4, under Q2
So we have:

Q1
Q2
Q3
Q4
Q5
Q6

We have max, 4 Levels.

I need sp which will display

Q1, A1, , , , , ,
Q1, A1, Q2,A2 , , , ,
Q1,A1,Q2,A2,Q3,A3, , ,
Q1,A1,Q2,A2,Q4,A4, , ,
Q1,A1,Q2,A2,Q4,A4 ,Q5,A5,
Q6,A6, , , , , , ,

Please help if you can.

Best,
Marta

 

[rt63]

Assuming only 4 levels and say u need to retrieve it for companyid = 10, this single query (union of 4 queries) should give you the desired output


-- 1st level
select a.question + ','+b.answer+', , , , , , ,'
from #tbl_Question a, #tbl_Answer b
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends is null
-- 2nd level
union
select c.part1+a.question + ','+b.answer+', , , , ,'
from #tbl_Question a, #tbl_Answer b ,
(select a.question + ','+b.answer+',' as part1 , a.Questin_ID
from #tbl_Question a, #tbl_Answer b
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends is null) c
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends = c.Questin_ID
-- 3rd level
union
select c.part2+a.question + ','+b.answer+', , ,'
from #tbl_Question a, #tbl_Answer b ,
(select c.part1+a.question + ','+b.answer+',' as part2 , a.Questin_ID
from #tbl_Question a, #tbl_Answer b ,
(select a.question + ','+b.answer+',' as part1 , a.Questin_ID
from #tbl_Question a, #tbl_Answer b
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends is null) c
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends = c.Questin_ID) c
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends = c.Questin_ID
-- 4th level
union
select c.part3+a.question + ','+b.answer+','
from #tbl_Question a, #tbl_Answer b ,
(select c.part2+a.question + ','+b.answer+',' as part3, a.Questin_ID
from #tbl_Question a, #tbl_Answer b ,
(select c.part1+a.question + ','+b.answer+',' as part2 , a.Questin_ID
from #tbl_Question a, #tbl_Answer b ,
(select a.question + ','+b.answer+',' as part1 , a.Questin_ID
from #tbl_Question a, #tbl_Answer b
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends is null) c
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends = c.Questin_ID) c
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends = c.Questin_ID) c
where b.Company_ID = 10
and a.Questin_ID = b.Question_ID
and a.Depends = c.Questin_ID
order by 1



RT

 

[mnasuto]

hi RT,

Can this code be used in SQL data?

Best,
M

 

[rt63]

Yes on any MS SQL version. (unless I am not understanding what you mean by SQL data).




RT

 

[EdVassie]

BOL has a good discussion on this. Look for 'hierarchical information' in the index. Basically, if you do not know the depth of your hierarchy, then you must build a temporary table to contain depth information.

OLAP services allow you to use recusive SQL, which can speed up such queries because you do not need the temporary table. Also, if you have DB2 for Windows you can do recursive SQL in the base DB2 product.

Finally, in the eample from rt63, you should use 'UNION ALL', not just 'UNION'. Every time you code just UNION, you force SQL to do a sort so SQL can eliminate duplicate records. 'UNION ALL' avoids the sort, and in the above example the sort should not be needed.

This posting is a personal opinion only, and may not reflect reality.